User ID's

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to make unique user Id's using excel or access.
I want to CONCATENATE a first name and last name into first initial last
name but if there are duplicates I want the second one to be first initial
last name 1.

ie: john doe jdoe
joe doe jdoe1
Help!
Thanks
 
Assume this data is in cols A & B, from row1 down:

john doe
joe doe
mary jane
peter doe
etc

Put in C1:

=IF(OR(A1="",B1=""),"",LEFT(A1,1)&B1&IF(COUNTIF($B$1:B1,B1)-1=0,"",COUNTIF($
B$1:B1,B1)-1))

Copy C1 down

Col C will yield:

jdoe
jdoe1
mjane
pdoe2
 
Max,
I thought I had it with this... but my data is producing this...
Cherry Cloud CCloud
Christopher Cloud CCloud1
Chad Cloud CCloud2
Mark Smith MSmith
Michael Smith MSmith1
john roberts jroberts
Vila smith Vsmith2

Notice vila smith has a 2 and he is the first vsmith.
Did I do something wrong?
 
Try this revised set-up ..

Data is in cols A & B, from row1 down:

Put in C1: =LEFT(A1,1)&B1

Put in D1:

=IF(OR(A1="",B1=""),"",TRIM(LEFT(A1,1)&B1&IF(COUNTIF($C$1:C1,C1)-1=0,"",COUN
TIF($C$1:C1,C1)-1)))

Select C1:D1, fill down

Col D should now yield the results you want
 
I am attempting to make unique user Id's using excel or access.
I want to CONCATENATE a first name and last name into first initial
last name but if there are duplicates I want the second one to be
first initial last name 1.

ie: john doe jdoe
joe doe jdoe1

Off-topic a bit, but hopefully you've thought ahead about what will happen
in the future when new people arrive or current people depart. If the first
"john doe" departs, for example. Most likely, you'll want still-in-use
id's to stay the same.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top