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

[robbertly]

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]

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]

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]

Give this macro a try...

[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]

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]

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.

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]

...this modification of Rick's code runs about 15 times faster for me.

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]

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]

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?

 

[robbertly]

Hello Rick,

Thanks for getting back so quickly.

To answer

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


Again, thanks for any help you can give on this.

Regards,

Robert