How do I set up a random name generator in excel

grich962

New Member
Joined
Apr 2, 2007
Messages
25
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
 

Some videos you may like

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
Joined
Apr 6, 2002
Messages
754
=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
Joined
Oct 20, 2008
Messages
682
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
Joined
May 9, 2009
Messages
16,623
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,428
Messages
5,414,429
Members
403,526
Latest member
swedeness50

This Week's Hot Topics

Top