- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to generate random positive numbers without duplicates in Excel?
In this article, the user will learn the method to generate random positive numbers without duplicate values in Excel. This article briefs learners on two strategies to perform this same task. The first example uses VBA code to generate unique random numbers, while the second example will perform the same task by using the kutool. The main point to understand while performing a task is that a single task can be done in many ways. So, learners should know which one should be used to generate the results accurately and precisely.
Example 1: To generate random number in excel without duplicates by using the predefined formula
Consider the below given Excel sheet, to generate the random number in Excel without generating duplicate values. In this example, VBA code is used to generate random values.
Go to the “Developer” tab, and then under the “Code” section select the option “Visual Basic”. This will open a new dialog box.
The new open dialog box contains the heading “Microsoft Visual Basic for Applications”. Consider the below depicted image for reference −
Go to the “Insert” tab option, and then click on “Module”. This will eventually open the code area.
In the opened code editor copy the below given code −
' method to calculate the unique random data Sub UniqueRandomList () ' Declaring required variables Dim nums(1 To 50) As Boolean Dim randNums(1 To 10) As Integer ' Change 10 to the number of random numbers user wants to generate Dim i As Integer Dim j As Integer ' Seed the random number generator Randomize ' for each loop expression For i = 2 To 50 ' Initialize the array to False nums(i) = False ' Next expression Next i ' Generate a list of unique random numbers For i = 2 To 10 ' Change 10 to the number of random numbers you want to generate ' do loop Do ' random numbers randNums(i) = Int(50 * Rnd + 1) ' loop for while Loop While nums(randNums(i)) = True ' Mark the number as used nums(randNums(i)) = True Next i ' Print the list of unique random numbers on a worksheet For j = 2 To 10 ' Change 10 to the number of random numbers you want to generate Range("A" & j).Value = randNums(j) Next j End Sub
Please use proper code indentation to generate results without any errors. Snapshot for required code −
Go back to the selected Excel sheet area, and check a list of Excel unique values that will be generated there automatically. Consider the below-depicted image for proper reference −
Example 2: To generate random numbers in Excel without duplicates by using the kutool
Again consider the same Excel sheet. Go to the “Kutools” tab → ”Editing ” under the “Editing” option, and choose “Insert”. After that select the “Insert Random Data” dialog box in Excel.
In the “Insert Random Data” dialog box. Select the “Integer” section. In the “Integer” section, under the “From” label set the initial range value. For this example, let’s set the value to 1. After that in the “To” label enter the last data value. For this example, the considered value is 10. Select the tick to generate unique values only, and finally click on “Ok”.
This will display the set of required random numbers in the Excel sheet. Consider below the given Excel sheet for random values.
This article allows the users to understand the process to generate unique random numbers. The first method involves the use of VBA code, while the second method focuses on using the “Kutool” to generate the unique random values from the provided data range.
Kickstart Your Career
Get certified by completing the courseGet Started