Excel: Generate Unique Random Numbers

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

Back to: Excel Custom Function/Formulas . Got any Excel/VBA Questions? Free Excel Help

This UDF will generate x unique random numbers between any 2 numbers you specify. Many thanks to J.E. McGimpsey for modifying this to work on more than 10 numbers.

The Code

Function RandLotto(Bottom As Integer, Top As Integer, _                    Amount As Integer) As String    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        RandLotto = RandLotto & " " & iArr(i)    Next i        RandLotto = Trim(RandLotto)    End Function

To use this UDF push Alt+F11 and go Insert>Module and paste in the code. PushAlt+Q and save. The Function will appear under "UserDefined" in the Paste Function dialog box (Shift+F3).Use the Function in any cell as shown below.

=RandLotto(1,20,8)
This would produce 8 unique random numbers between 1 and 20

See Also: Excel Duplication Manager Add-in |Excel Number Manager Add-in |Excel Text Manager Add-in |Excel Named Range Add-in Manager |Excel OzGrid Plus Add-in |Excel Time Sheet | Excel Time Wage and Pay book

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates