Auto-Generate random set of four numbers without duplicates?

Welcome to the forum.

In A1 type Number
In B1 type Duplicate Digit

In A2 type this formula:

=TEXT(ROW()-2,"0000")

In B2 type this formula:

=SUM(--(LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},"aa"))>5))>0

Fill both formulas down to row 10001.


You now have a list of every possible 4 digit number from 0000 to 9999 but these numbers contain duplicate digits.

In D1 type Duplicate Digit
In D2 type FALSE
In G1 type Number

Select A1:B10001
Go to the Data tab and click on "Advanced"
An advanced filter window will appear. Fill it in as follows:

Action = Copy to another location
List range = A1:B10001
Criteria range = D1:D2
Copy To = G1

Click on OK. You now have your list of 4 digit numbers which do not contain duplicate digits. The list should go from G1:G5041 (including the header). You do not need to repeat this process again.

Now, you can randomly pick one of those numbers using the RANDBETWEEN() function.
In J1 type in Random Number
In J2 type in this formula:

=INDEX(G2:G5041,RANDBETWEEN(1,5040))

This formula will return a random number from your list. Each time you press F9 it will randomly pick a number from the list.