This post will guide you how to generate a list of random numbers without duplicates in Excel. How do I generate random numbers without duplicates with a formula in Excel.

1. Generate a List of Random Numbers without Duplicates

If you want to create a list of random numbers without duplicates in a range of cells, you can create a list of numbers in sequential order, and create a list of random numbers with RAND function in another column. Then you can use sort function to sort those list of random numbers. Then the list of random numbers in column A is created. Let’s do the detailed steps:

#1 type one number in Cell A1, assuming that you want to create random numbers and the maximum number is 10. So type the number 1 in cell A1.

#2 go to HOME tab, click Fill command under Editing group. And select Series from the pop up menu list. The Series dialog will open.

#3 type the maximum number 10 in the Stop value text box. And select Columns radio button.  Click OK button.

#4 a list of number has generated in the column A.

#5 type the following formula into another column B to generate another list of random numbers, and then drag the AutoFill Handle over other cells.

=RAND()

#6 select all random numbers in column B, and go to HOME tab, click Sort & Filter command under Editing group. Then select Sort Smallest to Largest or Sort Largest to Smallest from the pop up menu list.

A List of random numbers has been generated in the column A as you need.

2. Generate a List of Random Numbers without Duplicates Using VBA Macro

Now, let’s delve into the second Method, which involves writing a VBA Macro to generate a list of random numbers without duplicates. This method offers greater flexibility and is ideal for those who need to perform this task frequently or as part of a larger automation process.

To start, press ALT+F11 to open the Visual Basic for Applications editor. This integrated development environment is where we’ll write and manage our macros.

Within the VBA editor, click on Insert and select Module from the dropdown. This will create a new module in your VBA project, which serves as a container for our macro code.

Sub GenerateUniqueRandomNumbers()
    Dim selectedRange As Range
    Dim randomNumbers As Collection
    Dim randomNumber As Long
    Dim i As Long
    Dim count As Long
    Dim exists As Boolean

    ' Prompt user to select a range
    Set selectedRange = Application.InputBox("Click on the range where you want to fill unique random numbers.", "Select Range", Type:=8)
    
    ' Initialize a Collection to store unique random numbers
    Set randomNumbers = New Collection
    
    ' Generate random numbers until the collection has the same number of items as the selected range
    count = 0
    Do While count < selectedRange.Cells.count
        randomNumber = Int((100 * Rnd()) + 1) ' Generates a random integer between 1 and 100
        exists = False
        
        ' Check if the random number already exists in the collection
        For i = 1 To randomNumbers.count
            If randomNumbers.Item(i) = randomNumber Then
                exists = True
                Exit For
            End If
        Next i
        
        ' If the number does not exist, add it to the collection
        If Not exists Then
            randomNumbers.Add randomNumber
            count = count + 1
        End If
    Loop

    ' Fill the selected range with unique random numbers
    For i = 1 To randomNumbers.count
        selectedRange.Cells(i).Value = randomNumbers.Item(i)
    Next i
End Sub

 Once written, save the macro for future use.

Press ALT + F8, select GenerateRandomNumbersWithoutDuplicates from the list, and click Run.

The macro will prompt you to select a range of cells where you want the random numbers to be generated. After you select the range, the macro will fill it with unique random numbers.

3. Video: Generate a List of Random Numbers without Duplicates

This Excel video tutorial, we’ll explore two effective methods: using a straightforward formula and employing a VBA Macro for more advanced control and automation.

4. Related Functions

• Excel RAND function
  The Excel RAND function returns a random real number that is greater than or equal to 0 and less than 1. The syntax of the RAND function is as below:=RAND ()…