kacor

asked on

# Fill a range with random numbers

Hi Experts,

I want to fill a range (for example A2 : A10) with integer numbers (for example from 2 up to 10) which may occur only once in this range.

Any suggestions appreciated.

Thanks kacor

I want to fill a range (for example A2 : A10) with integer numbers (for example from 2 up to 10) which may occur only once in this range.

Any suggestions appreciated.

Thanks kacor

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

Actually, changing one of my previous solutions was a quick process.

Please find attached a workbook containing the following code:

Please run the "Test" subroutine to create random numbers in the range [A2:A10].

BFN,

fp.

Q-28129926.xls

Please find attached a workbook containing the following code:

```
Option Explicit
Public Sub Select_Unrepeated_Random_Numbers(ByRef lngArray() As Long, _
Optional ByVal lngMinimum As Long = 0&, _
Optional ByVal lngMaximum As Long = 0&)
' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28129926.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID: Q_28129926
' Question Title: Fill a range with random numbers
' Question Asker: kacor
' Question Dated: 2013-05-16 at 13:13:07
'
' Expert Comment: fanpages
' Copyright: (c) 2013 Clearlogic Concepts (UK) Limited / N.Lee [ http://NigelLee.info ]
'
' Based on...
'
' [ http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21743518.html ]
'
' Question Channel: Home \ All Topics \ Programming \ Languages \ Visual Basic
'
' ID: Q_21743518
' Question Title: Random generator not repeating numbers. How to stop it?
' Copyright: (c) 2006 Clearlogic Concepts (UK) Limited / N.Lee [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------
Dim blnWend As Boolean
Dim lngLoop As Long
Dim lngIndex As Long
Dim lngValue As Long
Dim objDictionary As Object
Dim vntKeys As Variant
On Error Resume Next
If lngMinimum = 0& And lngMaximum = 0& Then
lngMinimum = LBound(lngArray)
lngMaximum = UBound(lngArray)
End If
If lngMaximum - lngMinimum < UBound(lngArray) - LBound(lngArray) Then
Exit Sub
End If
Erase lngArray()
Set objDictionary = CreateObject("Scripting.Dictionary")
Randomize
While Not (blnWend)
DoEvents
lngValue = CLng(Int(Rnd() * (lngMaximum + 1& - lngMinimum))) + lngMinimum
If Not (objDictionary.Exists(lngValue)) Then
objDictionary.Add lngValue, objDictionary.Count + 1&
blnWend = (objDictionary.Count = UBound(lngArray) + 1& - LBound(lngArray))
End If
Wend
vntKeys = objDictionary.Keys
lngIndex = -1&
For lngLoop = LBound(lngArray) To UBound(lngArray)
lngIndex = lngIndex + 1&
lngArray(lngLoop) = vntKeys(lngIndex)
Next lngLoop
Set objDictionary = Nothing
End Sub
Public Sub Test()
Dim lngArray(8&) As Long ' 9 Numbers [2..10]
Dim lngLoop As Long
Call Select_Unrepeated_Random_Numbers(lngArray, 2&, 10&)
Cells.ClearContents
For lngLoop = LBound(lngArray) To UBound(lngArray)
Cells(lngLoop + 2&, 1) = lngArray(lngLoop)
Next lngLoop
End Sub
```

Please run the "Test" subroutine to create random numbers in the range [A2:A10].

BFN,

fp.

Q-28129926.xls

Without using VBA, you can generate a non-repeating list of random integers using this array-entered formula:

=INDEX(Integers,RANK(RandomNumbers,RandomNumbers))

Integers is a named range containing the numbers 2 through 10 (or whatever) that you wish to pick from

RandomNumbers is an equivalent list of random numbers

To array-enter a formula:

1) In this case, select all the cells that will be using the formula

2) Paste the formula in the formula bar (or click in the formula bar if the formula is already there)

3. Hold the Control and Shift keys down, then hit Enter

4. Release all three keys

Excel should respond by adding curly braces { } surrounding your formula.

To get a new randomized arrangement of integers, hit F9 (or enter data) to force a recalculation of the random numbers.

RandomizedQ28129926.xlsx

=INDEX(Integers,RANK(Rando

Integers is a named range containing the numbers 2 through 10 (or whatever) that you wish to pick from

RandomNumbers is an equivalent list of random numbers

To array-enter a formula:

1) In this case, select all the cells that will be using the formula

2) Paste the formula in the formula bar (or click in the formula bar if the formula is already there)

3. Hold the Control and Shift keys down, then hit Enter

4. Release all three keys

Excel should respond by adding curly braces { } surrounding your formula.

To get a new randomized arrangement of integers, hit F9 (or enter data) to force a recalculation of the random numbers.

RandomizedQ28129926.xlsx

You could remove the dependency on the Integers named range, Brad, if you changed your (array) formula to:

{=INDEX(ROW(C2:C10),RANK(RandomNumbers,RandomNumbers))}

I am still dubious about using such a technique on, say, 10,000 unique random numbers though :)

BFN,

fp.

{=INDEX(ROW(C2:C10),RANK(R

I am still dubious about using such a technique on, say, 10,000 unique random numbers though :)

BFN,

fp.

with integer numbers (for example from 2 up to 10)I considered using ROW($2:$10), but the above wording in the question suggested a more general approach would be preferred.

By referring to cells, the formula could even return text. For example, if the Integers range listed out the suits and values for a deck of cards, the formula could deal out one or more hands in a poker simulation.

...By referring to cells, the formula could even return text. For example, if the Integers range listed out the suits and values for a deck of cards, the formula could deal out one or more hands in a poker simulation.

I also toyed with something similar, but thought that simply adding a VLOOKUP (or INDEX/MATCH combination) formula in an adjoining cell could return text instead of numbers, if there was a "lookup table" for the text with an associated number within the random number range. My initial thought was answers for multiple choice questions (ensuring that at least one of the random responses was the correct answer, of course).

Actually, I do recall reading a thread about producing a deck of cards before:

[ http://www.excelforum.com/excel-general/761712-using-rand-to-simulate-dealing-cards.html ]

Also, for interest, here is a Previous Asked Question I contributed to:

"How can I return a list of ASELECT values within a range I specify and an average I specify"

[ https://www.experts-exchange.com/questions/20769947/How-can-I-return-a-list-of-ASELECT-values-within-a-range-I-specify-and-an-average-I-specify.html ]

The workbook that was available for download has long since been removed, but I could attach it within that (or this) thread now, if you think it useful.

BFN,

fp.

PS. Brad: Is the site ExcelAnswers.com just "screen scraping" Experts-Exchange.com?

[ http://www.excelanswers.com/general/Q_25270367-How-can-I-return-a-list-of-ASELECT-values-within-a-range-I-specify-and-an-average-I-specify.jsp ]

[ http://www.excelanswers.com/general/Q_25270367-How-can-I-return-a-list-of-ASELECT-values-within-a-range-I-specify-and-an-average-I-specify.jsp ]

Yes. ExcelAnswers.com is regurgitating content from Experts-Exchange without getting permission. They aren't the only ones to do that.

ASKER

Thanks for this ideas!

kacor

kacor

You are very welcome, kacor.

Did you use one of the solutions in the previous two Question threads?

BFN,

fp.

Did you use one of the solutions in the previous two Question threads?

BFN,

fp.

There are a few approaches to take, & the solution provided (if needed, after you have read the contents of the previous two questions) could be tailored to your future/ongoing requirements.

For example, if you needed 10,000 unique random numbers, then the method used to generate these could be different to a method if you only required nine.