Function Problems

How to Generate Non-Repeating Random Numbers with RANDARRAY

Chanuka Geekiyanage Oct 16, 2026 871 views

Question details

The user needs to generate a sequence or a specific amount of random numbers without any duplicate values.

How to Generate Non-Repeating Random Numbers with RANDARRAY

Product: Spreadsheet
Device & OS: not provided
Scenario: Creating randomized lists for raffles, unique identifiers, or random sampling where duplicate entries cannot be present.
Observed behavior: The standard RANDARRAY function frequently outputs duplicate numbers in its array, requiring a combination of other dynamic array functions to force unique results.

Before you start

Ensure your spreadsheet software supports dynamic array functions. You will need access to SEQUENCE, SORTBY, and RANDARRAY, which are available in newer software versions.

Solution 1Recommended

Randomize a Defined Sequence of Numbers

Use a combination of SORTBY, SEQUENCE, and RANDARRAY to shuffle a defined list of sequential numbers, guaranteeing no repeats.

By generating a strict mathematical sequence first and then sorting it by a parallel array of random numbers, you completely eliminate the chance of duplicates.

Select the Target Cell

Click on the empty cell where you want your list of non-repeating random numbers to begin.

Enter the Formula

Type the formula =SORTBY(SEQUENCE(20), RANDARRAY(20)) into the formula bar. Replace '20' with the maximum number you wish to generate.

Generate the Array

Press the Enter key. The formula will automatically spill down the column, outputting the numbers 1 through 20 in a completely random order.

Dynamic Spill: You only need to enter the formula in the top cell; the results will automatically fill the necessary cells below it.

Solution 2

Select a Subset of Unique Random Values from a Larger Range

Use the TAKE function alongside SORTBY, SEQUENCE, and RANDARRAY to pull a fixed number of unique random values from a much larger pool.

Powerful Spreadsheet Tool

Effortlessly Manage Dynamic Arrays with WPS Office

WPS Spreadsheet fully supports advanced dynamic array functions like RANDARRAY, SEQUENCE, and SORTBY, making complex data randomization seamless and highly efficient.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing Spreadsheet document.
2. Type the Formula: Click on any empty cell and type your unique randomization formula, such as =SORTBY(SEQUENCE(50), RANDARRAY(50)).
3. Let the Data Spill: Press Enter. WPS Spreadsheet will calculate the formula instantly and automatically spill the non-repeating random numbers into the adjacent rows.

Full compatibility with Microsoft Excel dynamic array formulas and functionsLightweight architecture for rapid calculation of large random datasetsUser-friendly interface with auto-complete suggestions for complex formulas

microsoft office alternative - wps office

Frequently Asked Questions

Why does the standard RANDARRAY function generate duplicate numbers?

RANDARRAY generates independent random values for each individual cell within the specified array. Because the calculations do not reference one another, there is always a mathematical probability that identical numbers will be produced.

How do I stop my random numbers from recalculating every time I edit the sheet?

Dynamic functions like RANDARRAY and SORTBY are volatile, meaning they recalculate whenever a change is made to the document. To freeze the numbers, highlight the generated array, copy it, right-click the top cell, and choose 'Paste as Values' to overwrite the formulas with static text.

Can I generate non-repeating random numbers starting from a number other than 1?

Yes. You can customize the SEQUENCE function to start at any specific number. For instance, to randomize numbers from 50 to 100, you would use =SORTBY(SEQUENCE(51, 1, 50, 1), RANDARRAY(51)).

Excel & Data Problems