Excel Formula Help (Excel 2003) ~ Creating Names

boka

New Member
Joined
Oct 12, 2006
Messages
9
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 ?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
This seems to work

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.
 

boka

New Member
Joined
Oct 12, 2006
Messages
9
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
I assume you just need G1:
Code:
=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 ""
 

Forum statistics

Threads
1,136,272
Messages
5,674,755
Members
419,525
Latest member
helensesc

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top