How to Create Columns of Unique Sorted Random Numbers in Excel
Question details
The user needs to generate thousands of spreadsheet columns, each containing eight unique random integers from 1 to 100, sorted from smallest to largest.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is working with a large spreadsheet and needs to populate many columns with eight unique random numbers from 1 to 100, ordered ascending.
- Observed behavior
- The goal is to create up to thousands of columns where each column returns exactly eight unique random integers from 1 to 100, sorted smallest to largest.
Make sure your WPS Office Spreadsheet or Excel version supports dynamic array functions such as RANDARRAY, UNIQUE, SORT, and SEQUENCE. Select the target output range in advance so you can enter the formula across all columns at once.
Use a Single Dynamic Array Formula to Generate All Columns
Best for Microsoft 365, Excel 2021, and WPS Office Spreadsheet versions that support dynamic arrays; it creates eight unique sorted random numbers per column with one formula.
The formula generates more random values than needed, removes duplicates with UNIQUE, extracts the first eight values with INDEX and SEQUENCE, and sorts them with SORT.
Open your workbook and select the range where you want the columns, for example A1:GJH1 for 5000 columns.
Type =SORT(INDEX(UNIQUE(RANDARRAY(8^2,1,1,100,TRUE)),SEQUENCE(8))) while the range is selected.
Press Ctrl+Enter to enter the formula into every selected cell at once.
Select the generated range, press Ctrl+C, right-click, and choose Paste Special > Values to keep the current numbers.
Freeze the Generated Random Numbers as Static Values
Use this whenever you need to preserve the current random set and prevent Excel or WPS Office from recalculating the numbers.
Generate Unique Sorted Random Columns Directly in WPS Office Spreadsheet
WPS Office Spreadsheet supports Excel-compatible dynamic array formulas, so you can create thousands of columns of unique, sorted random numbers with one formula and optionally freeze the result by pasting as values.
- 1. Open WPS Office Spreadsheet: Launch WPS Office, click Spreadsheet, and open a blank or existing workbook.
- 2. Select the target output range: Select the first-row range where the columns should appear, such as A1:GJH1.
- 3. Enter the dynamic array formula: Type =SORT(INDEX(UNIQUE(RANDARRAY(8^2,1,1,100,TRUE)),SEQUENCE(8))) into the active cell.
- 4. Press Ctrl+Enter to fill all selected cells: Press Ctrl+Enter to enter the formula across the selected range and generate the columns.
- 5. Paste as values to freeze results: Select the generated range, press Ctrl+C, right-click, choose Paste Special > Values, and the random numbers stay fixed.
Frequently Asked Questions
Does this formula work in all Excel versions?
No. It requires Microsoft 365, Excel 2021, or a WPS Office Spreadsheet version that supports dynamic array functions such as RANDARRAY, UNIQUE, SORT, and SEQUENCE.
Why do my random numbers keep changing?
RANDARRAY recalculates when the workbook recalculates. Select the range, copy it, and paste as values to keep the current numbers fixed.
How do I create exactly eight unique numbers in each column?
The formula uses RANDARRAY(8^2,1,1,100,TRUE) to generate 64 random integers, UNIQUE to remove duplicates, INDEX with SEQUENCE(8) to take the first eight remaining values, and SORT to order them ascending.
Can I use a different range of numbers?
Yes. Change the 1 and 100 in RANDARRAY to your minimum and maximum values, and adjust SEQUENCE(8) if you need a different count of unique numbers per column.
