How to Generate Unique Random Numbers in Excel Without Reusing Values
Question details
The user needs to generate exactly 20 unique five-digit random numbers while ensuring no duplicates exist within the new batch and completely excluding a specific list of values already used and stored on another worksheet.
- Product
- Excel 365
- Device & OS
- not provided
- Scenario
- Creating a batch of new randomized identifiers or codes that must be entirely unique and cannot collide with an existing, previously used set of codes.
- Observed behavior
- Standard functions like RANDBETWEEN or RAND generate random values but do not natively prevent duplicates within their own output, nor do they exclude specific numbers from previous datasets.
Ensure you are using a spreadsheet software version that supports Dynamic Array functions (like Excel 365 or recent versions of WPS Office) and have your list of previously used numbers identified in a named range or specific column.
Use Dynamic Array Functions (LET, FILTER, SORTBY, RANDARRAY)
Combine modern array formulas to create a master list of all possible numbers, filter out the used ones, shuffle the remaining options randomly, and extract exactly the number you need.
This approach guarantees absolute uniqueness and perfectly respects your exclusion list without requiring helper columns. By leveraging the LET function, the formula remains readable and efficient.
Click the cell where you want the 20 numbers to appear. You will need to know the range of your used numbers (e.g., Sheet2!A1:A100). Keep this ready to plug into your formula.
Enter the following formula structure: =LET(all_nums, SEQUENCE(90000,1,10000), avail, FILTER(all_nums, COUNTIF(Sheet2!A1:A100, all_nums)=0), shuffled, SORTBY(avail, RANDARRAY(ROWS(avail))), INDEX(shuffled, SEQUENCE(20)))
Press Enter. The formula will automatically spill exactly 20 unique, non-repeating five-digit numbers into the cells directly below your selected cell.
Use Helper Columns and Filtering (For Older Excel Versions)
If you do not have access to modern dynamic array functions, you can generate an excess of random numbers and manually filter out duplicates and reused values.
Generate Unique Random Numbers Easily in WPS Spreadsheet
WPS Spreadsheet fully supports modern dynamic array functions like LET, FILTER, and RANDARRAY, allowing you to easily generate non-repeating random numbers. It offers flawless compatibility with your existing Excel files and complex array formulas.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx file containing the used numbers.
- 2. Input the array formula: Select an empty cell and type out the LET and FILTER formula to exclude your previously used data range.
- 3. Lock the generated values: Because random functions recalculate automatically, highlight the generated list, right-click to copy, and select 'Paste as Values' to finalize your 20 unique numbers.
Frequently Asked Questions
Why do my generated random numbers keep changing?
Functions like RANDARRAY, RAND, and RANDBETWEEN are 'volatile', meaning they recalculate every time any change is made to the worksheet. To stop them from changing, select the generated numbers, copy them (Ctrl+C), right-click the same cells, and choose 'Paste as Values' to permanently lock them.
Why are some of my five-digit numbers dropping the first zero?
Excel treats numeric entries mathematically, so numbers like '05432' are automatically simplified to '5432'. To keep the leading zero, you must treat the numbers as text. You can accomplish this by wrapping your output formula inside the TEXT function, such as =TEXT(YourFormula, "00000").
Can I use the LAMBDA function to simplify this process?
Yes. In Excel 365, you can use the Name Manager to define a custom LAMBDA function (e.g., 'MoreRandoms') that encapsulates the LET, FILTER, and RANDARRAY logic. This allows you to simply type =MoreRandoms(UsedRange, 20) anywhere in your workbook to instantly generate your numbers without retyping the complex array.
