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.

 

[erik.van.geit]

Hi pgc, nice formula

it certainly works for me

I always wonder how one finds something like that.
my method is using several columns, then nesting ... Same for you ?

 

[barry houdini]

Hi Erik

Are you asking me?

I think that most formulas I use are versions of something I've used before (or stolen from Aladin :wink: ). When they require a bit more thought I think they mostly click in my head first; then writing them down is the easy bit.

I like this one because somebody asked me a while back if you could do it with a single formula and I couldn't think of a way. Once I've seen it working I think I should have come up with that myself......

 

[erik.van.geit]

when doing eveythin in your head, it's a gift like:
being able to play 24 blind-chess-matches
knowing how many measures, notes, etcetera you composed when finishing a symphony (Bach could)

stolen from Aladin
all great masters started copying (parts of) works of other masters

 

[pgc01]

Hi Barry, thank you for your kind words.

Hi Erik

my method is using several columns, then nesting ... Same for you ?

Most of it comes out of ideas from formulas of Aladin and the more experienced users in this board, like you two.

As far as array formulas are concerned I think first of an algorithm, basically FOR loops, and then try to squeeze them into a formula.

It's just a pity you can't define at least one or two variables inside a formula. It would make some formulas so much more efficient, shorter and easier to read...

Cheers
PGC

 

[Johncobb]

Hi PGC,
I implemented your formula and I get repeated numbers.
You said that there won't be.
Any idea why I get repeated numbers?
Sorry for my late reply.
Regards
John

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

 

[erik.van.geit]

Hi, John,

It's sure I don't get any repeated numbers. (controlled with a formulasystem)
can you explain step by step what you did ?

best regards,
Erik

 

[erik.van.geit]

Perhaps this can help you to check yourself.
  C  D  
1    80 
2 43 1  
3 15 1  
4 64 1  
5 22 1  
6 12 1  
7 28 1  
8 17 1  
9 56 1  

Blad1
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
C2:C9 {=SMALL(IF(COUNTIF($C$1:C1,ROW($1:$80))<>1,ROW($1:$80)),1+INT(RAND()*(80-ROW()+ROW($C$2))))}
D1    =COUNTIF($D$2:$D$81,1)
D2    =COUNTIF($C$2:$C$82,C2)
D3:D9 =COUNTIF($C$2:$C$81,C3)

{=formula}:
 select first cell
 enter formula without {}
 confirm with Control-Shift-Enter
 then copy down & across

[Table-It] version 06 by Erik Van Geit

 

[Johncobb]

Thank you for your quick reply.
I made a mistake but I got it working now.
No more repeats.
Thank you very much for your help.
This forum is very helpful.
Thank you MrExcel.
Best regards,
John from downunder

Hi, John,

It's sure I don't get any repeated numbers. (controlled with a formulasystem)
can you explain step by step what you did ?

best regards,
Erik

 

[erik.van.geit]

Hi,

there was an error in the code I pasted earlier
it was pasting the formulas as a "whole array" instead of a "one-cell-arrayformula" pasted to the other cells

this will do

Sub tes2()

    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

kind regards,
Erik

 

[Fin Fang Foom]

=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.
PGC

Can your formula be modified to retrieve random names in column A?

Here what I did but no luck.

=INDEX($A$2:$A$100,SMALL(IF(COUNTIF($C$1:C1,ROW(1:40))<>1,ROW(1:40)),1+INT(RAND()*(40-ROW()+ROW($C$2)))))