Random numbers

[secondtry]

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?

 

[pgc01]

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]

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]

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]

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]

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

=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]

Thanks a ton everyone!

 

[erik.van.geit]

Hi, all

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

'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]

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]

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