Solved

Fill a range with random numbers

Posted on 2013-05-16
11
254 Views
Last Modified: 2013-05-16
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
Comment
Question by:kacor
  • 7
  • 3
11 Comments
 
LVL 35

Accepted Solution

by:
[ 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

by:[ fanpages ]
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

by:[ fanpages ]
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 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

Open in new window



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

BFN,

fp.
Q-28129926.xls
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 81

Expert Comment

by:byundt
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

by:[ fanpages ]
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

by:byundt
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

by:[ fanpages ]
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).

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

by:[ fanpages ]
ID: 39172948
0
 
LVL 81

Expert Comment

by:byundt
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

by:kacor
ID: 39173765
Thanks for this ideas!

kacor
0
 
LVL 35

Expert Comment

by:[ fanpages ]
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question