User ID's
 

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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"CCloud" wrote in message
...
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

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?
"Max" wrote:

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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"CCloud" wrote in message
...
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

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(COUNT IF($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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"CCloud" wrote in message
...

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?

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.