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.

Table of Contents

## 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 ()…

## Leave a Reply

You must be logged in to post a comment.