Generate a Unique List of Random Numbers With a Simple Formula
Sections:
Versatile Formula to Generate a Unique List of Numbers
Excel 365 Formula to Generate a Unique List of Numbers
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.
- 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.)
- In the column next to the numbered list, input =RAND() in the first cell and copy it down.
- Sort the column with the random numbers in it and that will randomly sort the original numbered column as well.
- 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
- Skill Level: All Levels
- |
- 5 hours+
How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.
- Skill Level: All Levels
- |
- 2 hours
Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...
Tutorial: How to generate random whole numbers (integers) that are between two numbers. This allow...
Tutorial: How to combine a list of data into one cell while putting a delimiter between each piece ...
Tutorial: How to get a column letter from a number in Excel using a simple formula. This is an ...
Tutorial: Quickly create a large list of numbers in Excel using the Fill Command. This will save ...
Tutorial: How to Sum values using an OR condition across multiple columns, including using OR with ...
