using random between formular part 2

[tonylpcs@yahoo.co.uk]

Hi Everyone,

i have a spreedsheet and need to be able to generate 20 randon numbers between 1 and 20 in cell D5 down to cells D24 but with each cell having a different number in it.

Ive had lots of help and have got very close but no cigar so far,

can someone please help me as to how i can do this?

bellow is the formula that has come closest so far but it wont generate 20 randon numbers for some reason it needs more numbers than i want to give it?

basicly i put a value in cell C3 (i.e 1) and a max value in e3 (i.e 20)

i have then put the following formulars is cell D5 down to D24

=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D4, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D6:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D5, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D7:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D6, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D8:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D7, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D9:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D8, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D10:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D9, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D11:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D10, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D12:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D11, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D13:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D12, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D14:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D13, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D15:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D14, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D16:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D15, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D17:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D16, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D18:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D17, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D19:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D18, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D20:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D19, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D21:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D20, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D22:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D21, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D23:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D22, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D24:D25)))
=LARGE(ROW(INDIRECT($C$3&":"&$E$3))*NOT(COUNTIF(D$4:D23, ROW(INDIRECT($C$3&":"&$E$3)))), RANDBETWEEN(1,$E$3-$C$3-ROW(D25)))

the problem is it requires a higher number than the number of lines so i cant get exactly 1 to 20

can someone please tell me where im going wrong?

thanks

Tony

 

[cjcobra]

I have a real easy way of doing this. I used it when making card games in VBA. Make two columns. First column will be 20 random numbers =RAND(). The second column will be numbered 1-20. When you "calculate" or f9, the first column will create a bunch of random numbers. Sort both columns by the random numbers, and bam, your second column is in a random order. copy-paste, done.

 

[tonylpcs@yahoo.co.uk]

hi thank you this sounds like it just might work,
i will give it a go!

Thonks

Tony

 

[tonylpcs@yahoo.co.uk]

Wow,
That has done exactly what i needed it to do thank you so much

Tony