Solved

Fill a range with random numbers

Posted on 2013-05-16
11
234 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
 
LVL 80

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 80

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 80

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now