create a table with unique numbers

Copper Contributor

I want to create next thing, but my knowledge is to narrow.

 

a table with 5000 columns and 8 rows

This table needs to be filled with random numbers starting from 1 to 100 (both included)

per column, the 8 numbers must be unique (1 to 100)

Per column, the numbers need to be in ascending order.

 

thanks in advance

2 Replies

@johanvl 

Do you have Microsoft 365 or Office 2021? If so:

Select 5000 cells in a row, for example A1:GJH1.

Enter the formula

 

=SORT(INDEX(UNIQUE(RANDARRAY(8^2, 1, 1, 100, TRUE)), SEQUENCE(8)))

 

Press Ctrl+Enter to confirm it and enter the formula in all selected cells.

The result will be updated each time Excel recalculates.

If you want to "freeze" the result:

Select the entire range (using the above example, A1:GJH8).

Copy it, then paste as Values.

Super, thanks a lot @HansVogelaar