I want random numbers generated without repeating

[Johncobb]

In my worksheet in column CC in C1:C20 I want Excel to ganerate 20 random numbers between 1 and 80 without repeating the same numbers.
At present I have the following formula entered and it works, but after I click F9 some repeated numbers are generated.

=INT(RAND()*(80-1)+1)

How can I change the formula or replace it?
Thank you in advance for your help.
Ragards to all supporters.
John.

 

[pgc01]

Hi all

Another solution, without auxiliary columns, with just excel's basic functions.

With C1 empty, write in C2

=SMALL(IF(COUNTIF($C$1:C1,ROW($1:$80))<>1,ROW($1:$80)),1+INT(RAND()*(80-ROW()+ROW($C$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 80)
You'll get random integers 1-80, without duplicates.

Cheers
PGC

 

[erik.van.geit]

BRAVO, PGC !!
seems to work very well !!

 

[Johncobb]

Thank you for your help.
VB looks good but I'm not that good at it, I don't know how to implement it.
Where should I enter it?
Is it complicated? If not you can explain and I will try.
Best regards,
John

Hello

FOr VB version how is this?

Dim MY_RND_NO(80) As Variant
Sub CREATE_RANDOM()
Randomize
Range("C1:C20").ClearContents
MY_COUNT = 1
Do Until MY_COUNT = 21
    NEW_NUMBER = Int(Rnd() * (80 - 1) + 1)
    If MY_RND_NO(NEW_NUMBER) <> "USED" Then
        Range("C" & MY_COUNT).Value = NEW_NUMBER
        MY_RND_NO(NEW_NUMBER) = "USED"
        MY_COUNT = MY_COUNT + 1
    End If
Loop
End Sub

 

[erik.van.geit]

Hi,

first your main question

Where should I enter it?

answer:
start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the following code or your code:

If you want to use VBA, then it would be good to use a quick solution.
It's not a big deal for little ranges to use one code or another, but I like good practice: you never know it will be used for larger projects...

this macro is based on pgc01's formula
EDIT: this code was changed

Sub test()

    With Range("C2:C81")
    .Cells(1, 1).FormulaArray = _
    "=SMALL(IF(COUNTIF(R1C3:R[-1]C,ROW(R1:R80))<>1,ROW(R1:R80)),1+INT(RAND()*(80-ROW()+ROW(R2C3))))"
    .Cells(1, 1).Copy .Offset(1, 0).Resize(.Rows.Count - 1, 1)
    .Value = .Value
    End With

End Sub

Don't use methods which continue to pick items from the entire list. Always reduce the list, deleting the item which was picked.
The first code is much better, but sometimes you might want something "visual" (to play games or to explain how things work). This visual macro could be transformed to an arraysolution.

Sub pick_numbers()
'Erik Van Geit
'120516 pick numbers from list
'"visual method"
'for short runtime use array of formula methods instead

Dim I As Long
Dim DR As Long  'row to delete

Const FN = 1   'first number
Const LN = 80  'last number
Const NR = 20  '# of items to pick

    If FN > LN Or NR > LN - FN + 1 Then
    MsgBox "Lowest Number < Highest Number" & Chr(10) & "# items < Highest Number - Lowest Number +1", 48, "ERROR"
    Exit Sub
    End If

Application.ScreenUpdating = False

Columns("A:B").ClearContents
    With Range("A1:A" & LN - FN + 1)
    .Formula = "=ROW()+" & FN - 1
    .Value = .Value
    End With

For I = 1 To NR
Randomize Timer
DR = Int((LN - FN + 1 - I + 1) * Rnd) + 1
    With Range("A" & DR)
    Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = .Value
    .Delete Shift:=xlUp
    End With
Next I

Application.ScreenUpdating = False

End Sub

this is related
http://www.mrexcel.com/board2/viewtopic.php?p=1028229

best regards,
Erik

 

[Johncobb]

Thank you for your replies and your help.
The RAND works.
But in my system that I created this formula "=INT(RAND()*80+1)" works excelent for me ecxept that repeated numbers apear.
Is there any way to change or improve it so no repeated numbers would apear?
Best regards,
John

 

[erik.van.geit]

Thank you for your replies and your help.
The RAND works.
But in my system that I created this formula "=INT(RAND()*80+1)" works excelent for me ecxept that repeated numbers apear.
Is there any way to change or improve it so no repeated numbers would apear?
Best regards,
John

please read the thread entirely: this has all been discussed
there is a great solution posted by pgc01

 

[Johncobb]

I have used and implemented pgc01 code.
I get the numbers ok but F9 key doesn't work to update the numbers.
Any idea why?
Regards,
John

 

[erik.van.geit]

I have used and implemented pgc01 code.
I get the numbers ok but F9 key doesn't work to update the numbers.
Any idea why?
Regards,
John

you wrote "code"
do you mean the formulas or the code I made to apply them ?

should work with F9 when using formulas

 

[pgc01]

Hi Erik Thank you for your kind words.

Hi John

I have used and implemented pgc01 code.
I get the numbers ok but F9 key doesn't work to update the numbers.

What do you mean by code? My formula is to be introduced directly in the cells.

Please do a small test that will take you just some seconds to execute:
Open a new workbook.
Enter my formula in C2.
Don't forget to confirm the formula with with CTRL+SHIFT+ENTER and not just ENTER.
Copy it down some cells.

Now try F9 some times.
Does it work?

F9 should force a new set of number since the rand() function inside the formulas generates a new number.

Please tell me what happens.

Best regards
PGC

 

[barry houdini]

Hi pgc, nice formula

it certainly works for me