The sample random selection of your data can be done easily with the INDIRECT function.
You have a file with 19,000 rows. Each row has customer information: address, city, and country. You need to mail a coupon to 30 clients. To select customers randomly, follow these steps:
Step 1: Create a List of Random Numbers
First, create a list of 30 random numbers. You have two Excel functions for this:
- RANDBETWEEN(min_value, max_value)
- RANDARRAY(rows, columns, min, max, integer)
Both functions give random numbers between min and max values. RANDARRAY returns multiple numbers based on the row argument.
Convert the random number result to values to avoid regeneration. Copy and paste as values after creating random numbers. See this demo:
Step 2: Point to a Cell in Your Database
Next, write a formula to point to row 2’s LastName value.
We want the formula to return data from a random row, like row 16,308.
Step 3: Customize Your Cell Reference
Combine the reference of column B with a row number generated by RANDBETWEEN. Use the INDIRECT function:
- Write the INDIRECT function.
- Keep the column reference in double-quotes.
- Use the & sign.
- Select the cell with the random number.
To return a country value, change the column reference. In this example, use column F.
Related Articles
Tutorial Video
If you need further help to extract sample randomly, check out the following video:
03/06/2022 @ 02:32
May I suggest instead of using the Volatile INDIRECT function as you did with this..
=INDIRECT("H"&J2)
to use the non-Volatile INDEX function like this instead...
=INDEX(H:H,J2)
05/06/2022 @ 21:47
Very good point Rick