New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Assign Random Whole Numbers

Macros, VBA, Excel Automation, etc.

Assign Random Whole Numbers

Postby Donrm1 » Tue Dec 13, 2011 1:19 pm

Greetings,
In Excel 2010 I have 5 columns and 40 rows for 200 cells. I need to generate random numbers of 1 - 200 in the cells with no repeat of the numbers. Is this possible?

(Tipping Hat) T.I.A.
  • 0

Donrm1
Rookie
 
Posts: 6
Joined: Dec 13, 2011
Reputation: 0

Re: Assign Random Whole Numbers

Postby Sisyphus » Wed Dec 14, 2011 4:08 am

Hi,

Code: Select all
=INT(RAND()*($B$2-$A$2)+$A$2)


The above formula will place a number between A1 abd B1 into the cell it occupies. Each time you open the worksheet or press F9 the number will change.

Numbers generated by RAND() are "evenly distributed". Therefore you are bound to have duplicates in almost every attempt. Anything else would go against the nature of their being "random".

To achieve the desired result you should have a little code work on it:
1. Convert the formulas to real numbers
2. Find out which numbers are missing
3. Look for a duplicate and replace it with the first missing number
4. Repeat until the store of missing numbers is exhausted.

The missing numbrs are in some kind of sequence, but the location of the duplicates they will replace is purely random. Therefore the location of the replaced numbers would also be random.

Of course, since you can't do without code anyway, you might prefer to fill the range by code to begin with. :twisted:
Have fun!
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Assign Random Whole Numbers

Postby User_5 » Wed Dec 14, 2011 6:05 am

You can't get any duplicates because each time a random number is used,
its removed from the collection until the collection is empty.
The Randomize statement gets the seconds off the system clock and gives a new
seed to the random sequence.

Paste this in a VBA module and run it by pressing F5.

Code: Select all
Sub RandNoRepeat()
  Dim NumArray(1 To 200)
  Dim RandGroup As Collection
  Dim RandNum As Long
  Dim UsedNum As Long
  Dim Element As Variant
  Dim AddWorkSheet As Worksheet
  Dim i As Long, j As Long, k As Long
 
  Application.ScreenUpdating = False
  'Comment out the next line if you don't want to add
  ' a new worksheet each time you run the code.
  Set AddWorkSheet = ThisWorkbook.Worksheets.Add

  Set RandGroup = New Collection
 
  For k = 1 To UBound(NumArray)
    NumArray(k) = k
  Next k
   
  i = 1
  For Each Element In NumArray
      RandGroup.Add Element
      i = i + 1
  Next Element
 
  With ActiveSheet
    For i = 1 To 40
      For j = 1 To 5
        Randomize
        UsedNum = Int(Rnd() * RandGroup.Count + 1)
        RandNum = RandGroup(UsedNum)
        .Cells(i, j) = RandNum
        RandGroup.Remove (UsedNum)
      Next j
    Next i
  End With
Application.ScreenUpdating = True
End Sub


'Press Alt+F11 to go to Excel.
'If you want to see your results in an array of 20 rows and 10 columns so you don't have to scroll
'down to see your results, change these two lines:

' For i = 1 To 40 ...... change the 40 to 20
' For j = 1 To 5 .... change the 5 to 10
  • 0

User_5
Regular
 
Posts: 34
Joined: Dec 13, 2011
Reputation: 0

Re: Assign Random Whole Numbers

Postby Donrm1 » Thu Dec 15, 2011 10:35 am

Thanks to you both...I have used the suggestion from User_5, works just like I need it to. :D

(Tipping Hat) Thanks again, hope I can help in return someday.

Don M.
  • 0

Donrm1
Rookie
 
Posts: 6
Joined: Dec 13, 2011
Reputation: 0

Re: Assign Random Whole Numbers

Postby User_5 » Fri Dec 16, 2011 2:25 am

You're welcome. Glad to help.
  • 0

User_5
Regular
 
Posts: 34
Joined: Dec 13, 2011
Reputation: 0


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 72 guests