# How do I set up a random name generator in excel

#### grich962

##### New Member
I need a formula that generates random names that i have in column a (a2:a30)
I need the name to be in b1 to j1 there can't be any duplicate names

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### mjbeam

##### Well-known Member
=concatenate(char(int(rand()*26)+65),char(column()+64),char(int(rand()*26)+65),char(int(rand()*26)+65),char(int(rand()*26)+65),char(int(rand()*26)+65),char(int(rand()*26)+65),char(int(rand()*26)+65),char(row()+64),char(int(rand()*26)+65))

#### crook_101

##### Well-known Member
Hi,

So you need to select random names from a2:a30

You will need an additional column to do this using formulas...

Assuming you can use column B, hide it and use C as your output column, do the following:

B1 copied down

Code:
``=RAND()``
and the formula in C1 copied down

Code:
``=INDEX(\$A\$1:\$A\$30,RANK(B1,\$B\$1:\$B\$30))``
HTH
Ian

#### T. Valko

##### Well-known Member
I need a formula that generates random names that i have in column a (a2:a30)
I need the name to be in b1 to j1 there can't be any duplicate names
Try this...

Cell A1 must not contain one of the names that is in the range A2:A30.

Enter this array formula** in B1 and copy across to J1:

=INDEX(\$A2:\$A30,SMALL(IF(ISNA(MATCH(\$A2:\$A30,\$A1:A1,0)),ROW(INDIRECT("1:29"))),INT(RAND()*(29-COLUMNS(\$A1:B1)+COLUMN(\$B1)))+1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.