How to get random sample in Excel with no duplicates

This tutorial demonstrates how to perform random sampling in Excel without duplicates. Solutions are provided for Excel 365, Excel 2021, Excel 2019, and older versions.

Previously, we explored methods for random selection using RAND and RANDBETWEEN, which can result in repeated values. This tutorial offers techniques to avoid duplicates.

Excel 365 & 2021: Random Selection from a List without Duplicates

This method leverages dynamic arrays, available in Excel 365 and 2021. The formula:

INDEX(SORTBY(data, RANDARRAY(ROWS(data))), SEQUENCE(n))

where n represents the desired sample size.

For example, to select 5 unique names from A2:A10:

=INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(5))

The sample size can be referenced from a cell (e.g., C2):

=INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(C2))

How it works: RANDARRAY generates random numbers; SORTBY shuffles the data based on these numbers; INDEX retrieves the first n values using SEQUENCE.

Excel 365 & 2021: Selecting Random Rows without Duplicates

This extends the previous method to select entire rows. The formula:

INDEX(SORTBY(data, RANDARRAY(ROWS(data))), SEQUENCE(n), {1,2,…})

where {1,2,…} represents the column numbers to extract.

To select random rows from A2:C10 (sample size in F1):

=INDEX(SORTBY(A2:C10, RANDARRAY(ROWS(A2:C10))), SEQUENCE(F1), {1,2,3})

The logic remains the same, but INDEX now uses both row and column indices.

Excel 2019 - 2010: Random Sampling

Since dynamic arrays are unavailable in older versions, a different approach is needed. For random selection from A2:A10:

1. Generate random numbers in B2:B10 using =RAND().
2. Extract the first random value in E2 using: =INDEX($A$2:$A$10, RANK.EQ(B2, $B$2:$B$10) COUNTIF($B$2:B2, B2) - 1)
3. Copy this formula down to select the desired number of values.

RANK.EQ ranks the random numbers; COUNTIF handles duplicates, ensuring unique selection.

Preventing Sample Changes

Because RAND, RANDBETWEEN, and RANDARRAY are volatile, the sample will change with worksheet edits. Use "Paste Special > Values" to convert formulas to static values.

Excel Random Selection (Rows, Columns, or Cells - All Versions)

The Ablebits Ultimate Suite provides a user-friendly interface for random selection. Select your range, choose rows/columns/cells, specify the sample size, and click "Select."

Downloads:

• Random sample without duplicates - formula examples (.xlsx)
• Ultimate Suite 14-day trial (.exe)

The above is the detailed content of How to get random sample in Excel with no duplicates. For more information, please follow other related articles on the PHP Chinese website!