Excel Formula Help (Excel 2003) ~ Creating Names

[boka]

Hello,

For a project, i have to generate 1000+ (and continue) random names in 'First name - Last name' format. Its almost impossible to write all those names one by one.

This is what that can be useful: i write 50 first names at a column, and 100 last names in a column, then set a formula to another sheet which can take a first name, then a last name (randomly) and make a full name (where, there cannot be any repeats).

what would be the formula ... or any workarounds that can do the similar job ?

 

[hatman]

This seems to work

Book1
	A	B	C	D
1	tom	Doe		tomDoe
2	****	Smith		tomSmith
3	harry	Yossarian		tomYossarian
4	polly	Baggins		tomBaggins
5		Al'Thor		tomAl'Thor
6		Atreides		tomAtreides
7		Harkonnen		tomHarkonnen
8		Sanche		tomSanche
9				dickDoe
10				dickSmith
11				dickYossarian
12				dickBaggins
13				dickAl'Thor
14				dickAtreides
15				dickHarkonnen
16				dickSanche
17				harryDoe
18				harrySmith
Sheet1

where D2 is =IF(MATCH(RIGHT(D1,LEN(D1)-FIND(" ",D1)),B:B,0)=COUNTA(B:B),INDEX(A:A,MATCH(LEFT(D1,FIND(" ",D1)-1),A:A,0)+1),LEFT(D1,FIND(" ",D1)-1))&" "&IF(MATCH(RIGHT(D1,LEN(D1)-FIND(" ",D1)),B:B,0)=COUNTA(B:B),INDEX(B:B,1),INDEX(B:B,MATCH(RIGHT(D1,LEN(D1)-FIND(" ",D1)),B:B,0)+1))

When you get to the end of the list, you get only last names... and then errors.

 

[hatman]

Better, if you need the final output to be in random order, here's how:

Book1
	A	B	C	D	E	F	G
1	tom	Doe		tomDoe		0.259245	harryYossarian
2	****	Smith		tomSmith		0.905055	tomSmith
3	harry	Yossarian		tomYossarian		0.665225	dickYossarian
4		Baggins		tomBaggins		0.450246	dickAtreides
5		Al'Thor		tomAl'Thor		0.289255	harrySmith
6		Atreides		tomAtreides		0.757647	tomHarkonnen
7		Harkonnen		tomHarkonnen		0.043636	harryAtreides
8				dickDoe		0.870051	tomBaggins
9				dickSmith		0.330842	harryDoe
10				dickYossarian		0.887592	tomYossarian
11				dickBaggins		0.671759	dickDoe
12				dickAl'Thor		0.555726	dickAl'Thor
13				dickAtreides		0.64487	dickBaggins
14				dickHarkonnen		0.823576	tomAl'Thor
15				harryDoe		0.237174	harryBaggins
16				harrySmith		0.671659	dickSmith
17				harryYossarian		0.386792	dickHarkonnen
18				harryBaggins		0.959306	tomDoe
19				harryAl'Thor		0.054186	harryAl'Thor
20				harryAtreides		0.788217	tomAtreides
21				harryHarkonnen		0.00134	harryHarkonnen
Sheet1

Each time you hit F9, the order of the names in column G refresh in a new random order...

 

[boka]

this works

thanks a lot

 

[boka]

hello again ...

can u please write the formula in 2nd post (for G)m because if i copy formula from the worksheet, i get an error (i get &amp, .. where there is none)

please write the formula as u did in first post.

thanks.

 

[hatman]

I assume you just need G1:

=INDEX(D:D,RANK(F1,INDIRECT("F1:F"&COUNTA(D:D)))+COUNTIF(F$1:F1,F1)-1)

For future reference, after pasting into your worksheet, you can do a Find/Replace of "amp;" with ""