# Non-repeating list of random numbers between 0001 and 9999, only need 2051 of the 9999

#### robbertly

##### New Member
Hi, I want to generate a list of non-repeating random numbers between 0001 and 9999, but I only need a total of 2051 of the 9999 I want to have 5 separate / individual columns, each with one set of non-repeating numbers. Any suggestions, comments, ideas welcome. Thanks

#### Rick Rothstein

##### MrExcel MVP
Hi, I want to generate a list of non-repeating random numbers between 0001 and 9999, but I only need a total of 2051 of the 9999 I want to have 5 separate / individual columns, each with one set of non-repeating numbers. Any suggestions, comments, ideas welcome. Thanks
How many random values should be placed in each column? I ask because 2051 divided by 5 equals 410.2 numbers per column, so not all columns can have the same number of random values.

#### robbertly

##### New Member
Hi Rick,

Thanks for following up.

Essentially I want to produce five columns, each containing 2,051 numbers from the range of 0001 to 9999.

So each column will have only 2,051 of the 9,999 possible numbers, with no repeating or duplicated numbers.

Each column is to be a unique set of 2,051 numbers in its own right, and can contain numbers contained in the other columns, the only proviso is that no column can contain repeating or duplicated numbers in its own column.

Hope this clarifies what I am trying to do.

Any ideas, suggestions appreciated.

Thanks

#### Rick Rothstein

##### MrExcel MVP
Give this macro a try...
Code:
``````[table="width: 500"]
[tr]
[td]Sub FiveColumnsOf2051UniqueRandomNumbers()
Dim C As Long, Cnt As Long, RandIndx As Long, Tmp As Variant, Nums As Variant
Nums = [ROW(1:9999)]
Application.ScreenUpdating = False
For C = 1 To 5
For Cnt = 9999 To 1 Step -1
RandIndx = Application.RandBetween(1, Cnt)
Tmp = Nums(RandIndx, 1)
Nums(RandIndx, 1) = Nums(Cnt, 1)
Nums(Cnt, 1) = Tmp
Next
Cells(1, C).Resize(2051) = Nums
Next
Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]``````

• robbertly

#### robbertly

##### New Member
Hello Rick,

Absolutely excellent. Worked perfectly.

Thank you so much for this, it will save me so much time and effort, I really appreciate it.

This is a great community and a great forum.

Please, keep up the good work here for people like me who need the expert advice/help.

Thanks,

Robert

#### Peter_SSs

##### MrExcel MVP, Moderator
With the size data you are producing, speed is probably not going to be an issue, but in case you have to repeat this a lot or eventually require larger sets of data or are just interested, this modification of Rick's code runs about 15 times faster for me.
Code:
``````Sub FiveColumnsOf2051UniqueRandomNumbers_v2()
Dim C As Long, Cnt As Long, rw As Long, RandIndx As Long, Tmp As Variant, Nums As Variant, Result(1 To 2051, 1 To 5) As Long
Application.ScreenUpdating = False
Nums = [ROW(1:9999)]
Randomize
For C = 1 To 5
rw = 0
For Cnt = 9999 To 7949 Step -1
RandIndx = 1 + Int(Rnd() * Cnt)
Tmp = Nums(RandIndx, 1)
Nums(RandIndx, 1) = Nums(Cnt, 1)
Nums(Cnt, 1) = Tmp
rw = rw + 1
Result(rw, C) = Tmp
Next
Next
Cells(1, 1).Resize(2051, 5) = Result
Application.ScreenUpdating = True
End Sub``````

• Rick Rothstein

#### Rick Rothstein

##### MrExcel MVP
...this modification of Rick's code runs about 15 times faster for me.
Rich (BB code):
``````Sub FiveColumnsOf2051UniqueRandomNumbers_v2()
Dim C As Long, Cnt As Long, rw As Long, RandIndx As Long, Tmp As Variant, Nums As Variant, Result(1 To 2051, 1 To 5) As Long
Application.ScreenUpdating = False
Nums = [ROW(1:9999)]
Randomize
For C = 1 To 5
rw = 0
For Cnt = 9999 To 7949 Step -1
RandIndx = 1 + Int(Rnd() * Cnt)
Tmp = Nums(RandIndx, 1)
Nums(RandIndx, 1) = Nums(Cnt, 1)
Nums(Cnt, 1) = Tmp
rw = rw + 1
Result(rw, C) = Tmp
Next
Next
Cells(1, 1).Resize(2051, 5) = Result
Application.ScreenUpdating = True
End Sub``````
Good modifications! Thanks!

We can make one more modification... probably does not speed things up noticeably, but the red highlighted line of code is no longer needed with your modifications and can be deleted.

#### robbertly

##### New Member
Hi Rick and Peter,

Thanks for the follow up on this, really appreciate it.

Following on from this, just one other question.

I want to use the macro to generate sets of numbers as described on 10 separate sheets within a workbook, with the sets of numbers on each sheet different to other sheets.

So in effect I want to use the Macro to insert the numbers on E9 on Sheet 1, then run the macro for a new set of numbers to appear on E9 Sheet 2, then run the Macro for a new set of numbers to appear on E9 Sheet 3, etc all the way to E9 Sheet 10.

Can this be done? Or do I need to use a separate Macro for each of the 10 sheets? If so, how do I use the macro above to do this, at present it only produces numbers on the first sheet but will not produce sets of numbers on each individual sheet it is run on?

Again, any suggestions or examples are very welcome.

Regards,

Robert

#### Rick Rothstein

##### MrExcel MVP
Following on from this, just one other question.

I want to use the macro to generate sets of numbers as described on 10 separate sheets within a workbook, with the sets of numbers on each sheet different to other sheets.

So in effect I want to use the Macro to insert the numbers on E9 on Sheet 1, then run the macro for a new set of numbers to appear on E9 Sheet 2, then run the Macro for a new set of numbers to appear on E9 Sheet 3, etc all the way to E9 Sheet 10.

Can this be done? Or do I need to use a separate Macro for each of the 10 sheets? If so, how do I use the macro above to do this, at present it only produces numbers on the first sheet but will not produce sets of numbers on each individual sheet it is run on?
One macro can do it all, but first, some questions for clarification...

1) When you say "with the sets of numbers on each sheet different to other sheets", do you mean the individual numbers on Sheet1 would not repeat on Sheet2, Sheet3, etc.? Or do you mean the same as your original question, but each column would be on a different sheet instead of having multiple columns on the same sheet?

2) Are we still talking 2051 numbers per column on each sheet?

3) The list will always start on cell E9 on each sheet, correct?

4) Will the 10 sheets already exist or will the code have to create them?

5) You show your sheet names with a space between the word "Sheet" and the number that follows... is that how your sheets are really named or are they named with the standard default Sheet1, Sheet2, etc. with no spaces?

Last edited:

#### robbertly

##### New Member
Hello Rick,

Thanks for getting back so quickly.

1) When you say "with the sets of numbers on each sheet different to other sheets", do you mean the individual numbers on Sheet1 would not repeat on Sheet2, Sheet3, etc.? Or do you mean the same as your original question, but each column would be on a different sheet instead of having multiple columns on the same sheet?

- Yes, I mean the same as the original question (and the original code provided), so each sheet would have 5 columns per sheet (as per your macro) over a total of ten sheets (5 x 10 = 50 in total) -

Just wondering if would it be possible to increase the number of columns from 5 to 10 in total per sheet? (so a total of 10 columns x 10 sheets = 100 columns in total)

2) Are we still talking 2051 numbers per column on each sheet?

- Yes, 2,051 numbers per column on each sheet.

3) The list will always start on cell E9 on each sheet, correct?

- Yes, starting on E9 with no space between columns, so E9, F9, etc to N9

4) Will the 10 sheets already exist or will the code have to create them?

- the sheets will already exist and will be styled to have the numbers start at E9

5) You show your sheet names with a space between the word "Sheet" and the number that follows... is that how your sheets are really named or are they named with the standard default Sheet1, Sheet2, etc. with no spaces?

- sorry about that, yes, the sheet names are the standard format Sheet1, Sheet2, etc as per default style

Regards,

Robert

1,081,421
Messages
5,358,584
Members
400,505
Latest member
JacquiT

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...