How to do random selection from list without duplicates

Answer

To perform a random selection from a list without duplicates in Excel, follow these steps. This method ensures each item is selected only once.

Step 1: Prepare your list

Place your items in a single column, for example, A2:A20. Ensure there are no blank cells in the range.

Step 2: Assign random numbers

1. In the column next to your list (for example, column B), enter the formula:

   =RAND()

2. Drag the formula down for all items in your list. This assigns a random decimal between 0 and 1 to each item.

Step 3: Sort the list by random numbers

1. Select both columns (the original list and the random numbers).

2. Go to the Data tab and click Sort.

3. Sort by the column containing the random numbers in ascending or descending order.

4. The items in column A are now shuffled randomly.

Step 4: Select the top N items

Take the top N rows from the shuffled list as your random selection. This guarantees no duplicates.

Step 5: Optional - Use a dynamic formula for automatic updates

To generate a dynamic list of unique random selections without manually sorting, use the SORTBY function (available in Excel 365 and Excel 2021): 

=SORTBY(A2:A20, RANDARRAY(ROWS(A2:A20)))

This formula sorts the list randomly and updates automatically whenever the sheet recalculates. Pick the first N items from this sorted array.

Notes

• RANDARRAY and SORTBY are not available in older Excel versions; in older versions, sorting manually by RAND() is necessary.

• For very large datasets, dynamic formulas reduce the risk of human error and maintain uniqueness efficiently.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report