When you have a huge list of customers, products, files, employees, .... extracting samples by copy-paste is a waste of time. This article will explain how to extract a perfect random list of data 👍😀
Let's say you have a list of 19,000 customers. You want to extract a random list of mail, name, and city (only these 3 columns).
Step 1 : Create a list of random number
The very first step to extracting a sample randomly is to create a list of random numbers, and this is very easy with the function RANDBETWEEN.
- Identify the first row of your dataset (here row 2)
- Identify the last row of your dataset (here row 19,001)
- Write the following formula
Step 2 : Copy the formula to increase the list
Copy-paste your formula to generate a list of random numbers. These numbers will be the Excel row number 😲
Step 3: Convert the formulas into values
Of course, you can't keep your formulas. Because each time you will refresh your worksheet, these formulas will re-generate new values. So you must convert your formulas with the option Copy-Paste Special (option Value) in this video.
Tips: With this technique, you can't avoid duplicates. So, the trick is to generate more random numbers that you need and remove duplicates 😉
Step 4: Use the INDIRECT function to customize a reference
The INDIRECT function helps you to create a custom function with variable values. And here, it's exactly what we need here (follow the link if you have never used this function)
Now, we will build a custom cell's reference with the result of the previous random number (in J2) to return the email (column H). The formula for the email is
Copy this formula for all your random numbers and you will have your list of email
For the country, just change the value of the column reference
Build the formula from another worksheet
Now, if you want to extract the value in a workbook that is not the one that contains the data, the formula is a little bit different. Here, you have to include also the worksheet reference.
Select any cell with an email
Now, keep only the reference of the sheet name and the column between double quotes, "Customer!H". The reference of the row will be taken in the cell B4 in our example
If there is something you didn't understand, have a look at the following video.