Generate a Unique List of Random Numbers With a Simple Formula

Add to Favorites
Author: | Edits: don

Sections:

Versatile Formula to Generate a Unique List of Numbers

Excel 365 Formula to Generate a Unique List of Numbers

Simple List in Excel 365

Simple List - Old Method

Versatile Formula to Generate a Unique List of Numbers

=IFERROR(LARGE(ROW($100:$999)*NOT(COUNTIF($C$6:C6,ROW($100:$999))),RANDBETWEEN(1,999-100-ROW(A1)+2)),"")

Min 100

Max 999

Range $C$6:C6

Change the Min value to the lowest possible value that you want to appear in the list. Change all occurrences of this number in the formula.

Change the Max value to the highest possible value that you want to appear in the list. Change all occurrences of this number in the formula.

Change the Range to the address of the first cell above the start of the list. Keep the exact format that it has now, with the first part in absolute references (with $ signs) and the second part in relative references.

Number of values is controlled by copying this formula down the column. Get the formula correct for the first entry and then copy it down as many times as you need to get as many unique random numbers as you need.

Excel 365 Formula to Generate a Unique List of Numbers

=INDEX(UNIQUE(RANDARRAY(10^2,1,100,999,TRUE)),SEQUENCE(10))

Min 100

Max 999

Number of Values 10

Change the Min value to the lowest possible value that you want to appear in the list.

Change the Max value to the highest possible value that you want to appear in the list.

Change the Number of Values to the number of values that you want to appear in the list. Change every bold occurrence of 10 in the formula.

Simple List in Excel 365

This generates a simple integer list of values that moves sequentially up or down.

=SORTBY(SEQUENCE(10),RANDARRAY(10))

Number of items to show is controlled by the number in the formula. Change 10 in both places to however many items you want to show.

Simple List - Old Method

This works for all versions of Excel.

  1. Create a list of numbers in a column. Put 1 in the first cell and 2 in the second cell and copy them down as far as you need. (This creates a simple numbered list.)
  2. In the column next to the numbered list, input =RAND() in the first cell and copy it down.
  3. Sort the column with the random numbers in it and that will randomly sort the original numbered column as well.
  4. Delete the random number column and then you are left only with the original column of unique numbers that are displayed in a random order.

 

 

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
Require a Unique List of Numbers in a Range in Excel
Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...
Generate Random Numbers within a Range in Excel
Tutorial: How to generate random whole numbers (integers) that are between two numbers.  This allow...
Quickly Combine a List of Values and Put a Delimiter Between Each Value in Excel
Tutorial: How to combine a list of data into one cell while putting a delimiter between each piece ...
Convert Column Number to Letter Using a Formula in Excel
Tutorial: How to get a column letter from a number in Excel using a simple formula. This is an ...
Quickly Create a Huge List of Numbers in Excel
Tutorial: Quickly create a large list of numbers in Excel using the Fill Command.  This will save ...
Sum Values that Equal 1 of Many Conditions across Multiple Columns in Excel
Tutorial: How to Sum values using an OR condition across multiple columns, including using OR with ...