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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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,884
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,884
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
 

Forum statistics

Threads
1,141,138
Messages
5,704,497
Members
421,353
Latest member
jekoxien15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top