When you have huge list of customers, products, files, employees, .... and you want to extract random data, most of the Excel users will copy-paste. But this technique is the worst.
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).
The usual copy-paste technique, is not the correct one because no one goes beyond the row 1,000 😉. But in just 3 steps, you can create a formula that will extract a perfect random list of data.
Step 1 : Create a list of random number
The very first step to extract random data is to create a list of random number, and this is very easy.
- 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 a random data.
Step 3: Convert the formulas in values
Of course, you can't keep your formulas. Because each time you will refresh your worksheet, thes 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 duplicate. So, the trick is to generate more random number that you need and remove duplicates 😉
Step 4: Use the INDIRECT function to customize a reference
The INDIRECT function helps you to create 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 number and you will have your list of email
And 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 who contain 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 understood, have a look at the following video.