Random interger set lists with no repeat numbers

Debbren

New Member
Joined
Jul 16, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
  2. Web
I am trying to get 10 random interger sets with no repeats using 1-200. I've been starting the process using the RANDBETWEEN function and that gets me one list of 200 numbers. I don't know where to go from there. Nothing seems to work. I am currently using Excel 2106. HELP!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
What do you mean by "10 random integer sets" (here I am concentrating on the word "sets"? How many sets? Does the non-repeat rule apply to individual sets or do you not want repeats for all the sets combined? Where are the sets to be placed? And the big question for me... can you use a macro for your solution or must it be a formula solution? (If a formula solution, I won't be able to help as VBA code is my "thing").
 
Upvote 0
Assuming you want to generate a set of (maxNum-minNum+1) integers in the range minNum to maxNum with no repeats, and are willing to use VBA, here is one possible solution. If minNum =1 and maxNum = 200, this will output a set to the range A1:A200 on the active sheet.
VBA Code:
Sub GenerateRandomSetsNoRepeats()
'generate a set of (maxNum-minNum +1) random integers from minNum to maxNum with no repeats
Const minNum As Long = 1: Const maxNum As Long = 200
Dim d As Object
Set d = CreateObject("Scripting.dictionary")
Do
    x = WorksheetFunction.RandBetween(minNum, maxNum)
    If Not d.exists(x) Then d.Add x, d.Count + 1
Loop While d.Count < maxNum
Application.ScreenUpdating = False
Range("A1:A" & d.Count) = Application.Transpose(d.keys)
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Just google for VBA Generate Unique Random Integers.
Ozgrid and E for Excel offer efficient algorithms without random loops.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top