|
Generate a Non-Repeating List of Random Numbers in Excel - UDF
Generate a series of non-repeating random numbers in Excel with this UDF (user defined function). This is a great function to use with statistical analysis and any other situation where you need to generate non-repeating random numbers. This is a very easy to use function and only has three arguments: one for the lowest possible random number; one for the highest possible random number; and one for the number of random numbers which you would like to display.
The one thing to note with this macro is that it will display all of the random numbers in the same cell as the function. The random numbers are separated by a space and this makes it easy to use text-manipulation functions, or the extract text UDF from this site, to pull the randomly generated numbers out of this cell.
Where to install the macro: Module
UDF to Generate a Non-Repeating List of Random Numbers in Excel
Function RANDNUMNOREP(Bottom As Integer, Top As Integer, Amount As Integer) As String
'This UDF will generate a non-repeating set of random numbers in excel and display those in the same cell as the function
Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer
Application.Volatile
ReDim iArr(Bottom To Top)
For i = Bottom To Top
iArr(i) = i
Next i
For i = Top To Bottom + 1 Step -1
r = Int(Rnd() * (i - Bottom + 1)) + Bottom
temp = iArr(r)
iArr(r) = iArr(i)
iArr(i) = temp
Next i
For i = Bottom To Bottom + Amount - 1
RANDNUMNOREP = RANDNUMNOREP & " " & iArr(i)
Next i
RANDNUMNOREP = Trim(RANDNUMNOREP)
End Function
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
I was getting bored at work and wanted to create my own Loto macro.
Loto in my country is 6 of 49 numbers
I made this macro, but the funny thing is that the mumbers are not random
It always gives e the same numbers as I open the document: 35, 27, 29, 15, 38, 1 then 38, 40 , 35, 3, 21, 43 and so on ....
So why isn't random random?
VB:
Dim numar, nr, i
Sub Loto()
nr = Array(0, 0, 0, 0, 0, 0)
For i = 0 To 5
Call genereaza
nr(i) = numar
Next i
MsgBox nr(0) & " " & nr(1) & " " & nr(2) & " " & nr(3) & " " & nr(4) & " " & nr(5)
End Sub
Function genereaza()
numar = Int((49) * Rnd + 1)
Call test
End Function
Function test()
For j = LBound(nr) To UBound(nr)
If numar = nr(j) Then
Call genereaza
End If
Next j
End Function
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
Hi all,
Here is what I am trying to do, I have numbers from 1-13, I would like to be able to automatically generate numbers from that range in (5) columns like A, B, C, D, E and these numbers when added will equal a known value.
For example:
I have the numbers, 1,2,3,4,5,6,7,8,9,10,11,12,13
the target value is: 12
Excel would do this:
2 2 2 4 2 or 4 2 2 2 2 or 2 4 2 2 2 or even 5 1 4 1 1 what I am trying to do is getting every possible set of numbers that would equal the target value.
I don't know if this is even possible, but I am hoping... Thanks in advance!!
Hello All,
I tried to create a 'one-click' code generator in VBA, and I kind of succeeded in this. The code it generates lookes like this: TEST1-12FEBTEST411LO1406
The problem though lies in de last four digits, its a random generated number thats not really random. When a bunch of codes is generated the last four digits turn up more than once, I attached a the file as example.
The VBA code to this contains a loop which I guess is incorrect, but Iam not able to fix it.
Maybe someone could help me out.
Thanks in advance.
TDA
Hi everyone,
I have a frequency list (frequency per year) resulted from previous calculation. What I want is to generate random number as many as the frequency in the specified year, so that every year I will get the same amount of random numbers as the frequency. It is kind of the opposite of FREQUENCY function (but with random numbers involved). I need to use the result for further calculation.
Could somebody help me to solve this problem??Thanks in advance!!
Hi,
Can someone please Help me in generating Random (non Duplicate) numbers as specified by me (macro allows me to choose the number of random numbers to create), from the data in a specified column in the activesheet (lets assume that the data is in Column "C" and the range is endless), and then save the same with a file name specified by me.
While the random numbers are saved in the new file, the macro also delets the generated numbers from the active sheet
Rgds,
Mohit
Hi everyone !
Can someody help me generate a sequence of numbers like
0
1
2 2
3 3 3
4 4 4 4
till 200
in excel with the help of a macro ??
My code is like this :
Code:
Sub Generate_Numbers()
Dim CountRow As Long
Dim cLastRow As Long
Dim i As Double
For CountRow = 1 To 201
Worksheets("random numbers").Cells(CountRow, 1).Value = i
i = i + 1
Next CountRow
End Sub
Thank you !
I want to shuffle a pack of playing cards using excel
Hello All,
I am working on a Random Cycle Count Generator that provides random SKU#s based on 3 separate columns of SKU listings. The user clicks a button to generate the SKU#s to cycle count for that day.
What I would like to see is a date stamp in the columns next(B,D,F) to the referenced SKU listing(A,C,E) based on which SKU#s are generated. This will let me see the last date that the SKU was generated. I would also like it to automatically save after generating.
I appreciate any help that you can provide. I looked on the forums for a while and couldn't find any obvious solution.
Thanks,
Thomas
Hi,
I run a lottery syndicate with nine people in who have all chosen two numbers each.
What I would like to have is a macro that randomly generates three lines of 6 numbers based on our 18 pre selected numbers i.e. use all 18 numbers with no number duplicated in any of the three lines generated (6 numbers per line).
Example numbers 2, 5, 7, 10. 11, 15, 17, 19, 20, 23, 24, 27, 29, 31, 32, 41, 44, 49,
Any assistance would be greatly appreciated
Many thanks in advance
Rob
N.B. Excel version 2003
Dear Forum,
I am using the VBA code below (bottom) to return random numbers for a selected area of cells where the range of random numbers is between 1 and the
total number of cells. Unfortunately sometimes the random numbers appear more than once and I would like to amend this to ensure duplicates are not included, any help would be greatly appreciated, thanks, Matt
Ps I have already tried substituting with VBUniqRandInt() but it returns #NAME? in every selected cell??, i.e;
Code:
Sub RandomTable4()
For Each r In Selection
r.Formula = "=int(VBUniqRandInt() *" & Selection.Count & "+1)"
Next
End Sub
Sub RandomTable4() ' but this returns duplicates
For Each r In Selection
r.Formula = "=int(Rand() *" & Selection.Count & "+1)"
Next
End Sub
|
|
