Random numbers

secondtry

New Member
Joined
Oct 10, 2006
Messages
32
I am working with the randbetween function and I was wondering if there was a way to avoid obtaining duplicate numbers. That is to say, if I have a column of random digits between 1 and some higher digit, is there a way to ensure each one comes up differently while maintaining its random characteristic?
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,850
Hi secondtry
Welcome to the board

You would make it easier if you would give more information when you post.

How many numbers do you have in that column?
You want random numbers between 1 and ?

If the answer to both questions is a small number, like < 100, you may have a formula solution. If you are talking about a big number of random numbers with no duplicates, maybe you should use vba.

Please post the details.

Kins regards
PGC
 

secondtry

New Member
Joined
Oct 10, 2006
Messages
32
Thanks for your direction pgc01.

I would like to have a column of 99 random numbers between 1 and 99 using the randbetween function to maintain whole numbers. I'm curious to know whether there is a formula to make this work as I am less familiar with vba for the time being. Please let me know if further clarification is needed.
 

PolarBear

Board Regular
Joined
Sep 26, 2005
Messages
215
MyValue = Int((99 * Rnd) + 1)
will generate random numbers between 1 and 99.

But in your first post, you said you wanted to avoid "duplicates" in the random numbers. Does this mean you want all the numbers from 1 to 99
distributed randomly down the column, with each number appearing once in the list? If that's the case, I think I would use VBA, build an array of Booleans, and set the corresponding element every time a random number was generated. You'd need to loop it in case there's a match. Something like:

dim DupeRandom(99) As Boolean

for i = 1 to 99
DupeRandom(i) = FALSE
next i ' this initializes the array to FALSE


for i = 1 to 99
myValue = Int((99 * Rnd) + 1)
if NOT DupeRandom(myValue) then DupeRandom(myValue) = TRUE
ActiveCell.Offset(i,0) = myValue
next

end sub
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

Here's one way to generate 1 to 99 randomly using a "helper" column

in A1

=RAND()

in B1

=RANK(A1,A$1:A$99)+COUNTIF(A$1:A1,A1)-1

both formulas copied down to row 99.

column B should now contain 1 to 99 at random. If worksheet is recalculated (hit F9) then numbers will change

...although I believe pgc has a solution without helper columns, see his contribution here

http://www.mrexcel.com/board2/viewtopic.php?t=231859
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,850
Hi secondtry

As Barry said I have already posted this solution but I post again because the cell before the numbers does not necessarily need to be empty, it can have text, like a label.

This is the solution:
With A1 empty or with text, write in A2

Code:
=SMALL(IF(COUNTIF($A$1:A1,ROW($1:$99))<>1,ROW($1:$99)),1+INT(RAND()*(99-ROW()+ROW($A$2))))
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

Copy down as many cells as you wish (until 99)
You'll get random integers 1-99, without duplicates.

Cheers
PGC
 

secondtry

New Member
Joined
Oct 10, 2006
Messages
32

ADVERTISEMENT

Thanks a ton everyone!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, all :)

PGC,
in the mean time I've made a procedure out of your formula

Code:
'rather slow for large numbers
Option Explicit

Sub generate_rnd(LowNr As Long, DiffNr As Long, Cnt As Long)

Dim WB As Workbook
Dim arr As Variant

    If Cnt > DiffNr Then
    MsgBox "Cnt > DiffNr", 48, "ERROR"
    Exit Sub
    End If
    
    If DiffNr > Rows.Count Then
    MsgBox "DiffNr > " & Rows.Count, 48, "ERROR"
    Exit Sub
    End If

Application.ScreenUpdating = False

Workbooks.Add
Set WB = ActiveWorkbook
    
    With Range("A" & LowNr + 1 & ":A" & LowNr + Cnt)
    .Cells(1, 1).FormulaArray = _
    "=SMALL(IF(COUNTIF(R" & LowNr & "C1:R[-1]C,ROW(R" & LowNr & ":R" & DiffNr + LowNr - 1 & "))<>1," & _
    "ROW(R" & LowNr & ":R" & DiffNr + LowNr - 1 & ")),1+INT(RAND()*(" & DiffNr & "-ROW()+ROW(R" & LowNr + 1 & "C1))))"
    .Cells(1, 1).Copy .Offset(1, 0).Resize(Cnt, 1)
    arr = .Value
    End With
    WB.Close False
    
ActiveSheet.Cells(1, 1).Resize(Cnt, 1) = arr

Application.ScreenUpdating = True

End Sub

Sub test()
'arguments
'lowest number
'"range": added to lowest number gives highest number
'how many numbers to generate
Call generate_rnd(10, 500, 30)
End Sub
run 'test'

kind regards,
Erik
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,850
Hi Erik

I've made a procedure out of your formula

I'll look into it. Although I know that this is not an efficient method I 'm curious if there's any difference in performance between the formula only and the vba.


By the way, I see that you crossed the 10000 posts barrier. Although the number by itsel is usually not important, in this case it is, because it's 10000 posts with professional quality and personal kindness. Congratulations!

Cheers
PGC
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi Erik

I've made a procedure out of your formula

I'll look into it. Although I know that this is not an efficient method I 'm curious if there's any difference in performance between the formula only and the vba.


By the way, I see that you crossed the 10000 posts barrier. Although the number by itsel is usually not important, in this case it is, because it's 10000 posts with professional quality and personal kindness. Congratulations!

Cheers
PGC
THANK you soo much for your kind words straight to the heart :)

I found out that your formula works correctly when you put it in the "corresponding" rows
the process of going to correct row, write correct formula, copy formulas to correct range and finally pasting values was worth some VBA

although now it seems so obvious, the most difficult part for me was editing the formula in VBA

would it be better to state the highest value instead of the "range" ?

greetings from Belgium,
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,109,380
Messages
5,528,357
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top