Extract random data

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.

Classical situation

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).

Excel file with customer details

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.

  1. Identify the first row of your dataset (here row 2)
  2. Identify the last row of your dataset (here row 19,001)
  3. Write the following formula

=RANDBETWEEN(2,19000)

Random number returns by the function RANDBETWEEN

Step 2 : Copy the formula to increase the list

Copy-paste your formula to generate a list a random data.

Copy the random formula

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.

Menu Paste Special value

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

=INDIRECT("H"&J2)

Creation of a custom reference with INDIRECT

Copy this formula for all your random number and you will have your list of email

List of email extract randomly

And for the country, just change the value of the column reference

=INDIRECT("G"&J2)

Return the country randomly

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

=Customer!H5

Reference from another worksheet

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

=INDIRECT("Customer!H"&B4)

Tutoriel video

If there is something you didn't understood, have a look at the following video.

Related posts


Permanent link to this article: https://www.excel-exercise.com/extract-random-data/

Leave a Reply

Your email address will not be published.