# random choose numbers

#### iaudio1233

##### Board Regular
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

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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?

Another variation:
In Cell A2
Code:
``=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

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

It gives me repeated numbers
sorry gives unique instances for the range A1 to A12 here.

Hi, all ,

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

Hi, all ,

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.

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

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.

Replies
8
Views
326
Replies
3
Views
498
Replies
12
Views
633
Replies
5
Views
2K
Replies
5
Views
380

1,196,409
Messages
6,015,103
Members
441,870
Latest member
kojack

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back