random choose numbers

[iaudio1233]

Hi,
there are 12 numbers from 1 to 12
there are 12 cells

requirement : choose 1 number from 1 to 12 and not appear again in any of the other 11 cells

eg.

a1 8
a2 12
a3 7
a4 5
a5 3
a6 11
a7 2
a8 9
a9 8
a10 1
a11 5
a12 4

kindly advise what function should be used ?

thanks

 

[cyrilbrd]

Given in A1: =RANDBETWEEN(1,12)
In A2: =SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:12")),$A$1:A1,0)),ROW(INDIRECT("1:12")),0),RANDBETWEEN(ROWS($A$1:A1)+1,12)) Ctrl + Shift + Enter not just enter on a PC or Command + Return on a MAC
copied down till A12.

Would that work for you?

 

[Danerida]

Another variation:
In Cell A2

=LARGE(ROW($1:$12)*NOT(COUNTIF($A$1:A1, ROW($1:$12))), RANDBETWEEN(1,12-ROW(A1)+1))

Also entered using CONTROL+SHIFT+ENTER as an array formula. Copied down to A13

Dan

 

[mas123]

Given in A1: =RANDBETWEEN(1,12)
In A2: =SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:12")),$A$1:A1,0)),ROW(INDIRECT("1:12")),0),RANDBETWEEN(ROWS($A$1:A1)+1,12)) Ctrl + Shift + Enter not just enter on a PC or Command + Return on a MAC
copied down till A12.

Would that work for you?

It gives me repeated numbers

 

[cyrilbrd]

It gives me repeated numbers

sorry gives unique instances for the range A1 to A12 here.

 

[iaudio1233]

Hi, all ,

thanks your prompt response.
Cyrilbrd's formula works exactly what I required.
Not yet tried another .
each cell is unique from 1 to 12.
GREAT !!!

 

[cyrilbrd]

Hi, all ,

thanks your prompt response.
Cyrilbrd's formula works exactly what I required.
Not yet tried another .
each cell is unique from 1 to 12.
GREAT !!!

Most welcome, and thank you for the feedback.

 

[mas123]

sorry gives unique instances for the range A1 to A12 here.

Sorry Dear
I put your formula in column B and 2nd formula in column A
and didn't change A range to B
so it gives me errors
but now it's very nice
thanks

 

[cyrilbrd]

Sorry Dear
I put your formula in column B and 2nd formula in column A
and didn't change A range to B
so it gives me errors
but now it's very nice
thanks

No worries, glad you got it sorted out.