Solved

# Fill a range with random numbers

Posted on 2013-05-16
276 Views
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
0
Question by:kacor
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 3

LVL 35

Accepted Solution

[ fanpages ] earned 500 total points
ID: 39171111
Hi kacor,

Here are two previous questions (out of a few more) that select random numbers (without repetition):

"Random Numbers - from a set list not repeating within that list"
[ http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_21146182.html ]
(September 2004)

and

"Random generator not repeating numbers. How to stop it?"
[ http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_21743518.html ]
(February 2006)

These were specifically written for questions within the Visual Basic topic areas, but the code is interchangeable with Visual Basic for Applications with Microsoft Excel (& Microsoft Office in general).

I contributed a solution (or two) within each question thread.

If you are not skilled in transposed the solutions provided in either of these two questions, please let me know & I will write something to meet your requirements.

BFN,

fp.
0

LVL 35

Expert Comment

ID: 39171122
PS. Is the extent of the range always going to be a relatively small quantity (&, hence, will the random sample of numbers always be a low quantity also)?

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.
0

LVL 35

Expert Comment

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

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 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
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
0

LVL 81

Expert Comment

ID: 39172216
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
0

LVL 35

Expert Comment

ID: 39172712
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.
0

LVL 81

Expert Comment

ID: 39172755
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.
0

LVL 35

Expert Comment

ID: 39172936
...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).

[ 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"
[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_20769947.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.
0

LVL 35

Expert Comment

ID: 39172948
0

LVL 81

Expert Comment

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

LVL 10

Author Closing Comment

ID: 39173765
Thanks for this ideas!

kacor
0

LVL 35

Expert Comment

ID: 39173870
You are very welcome, kacor.

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

BFN,

fp.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article youâ€™ll learn how to use ExcelToWord! to copy data,charts, shapes â€¦
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoâ€¦
###### Suggested Courses
Course of the Month6 days, 6 hours left to enroll