Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cells User Select Locked after upgrade to Excel 2002 | Excel Discussion (Misc queries) | |||
Print scrollable user form. | Excel Discussion (Misc queries) | |||
User forms "back" buttons | Excel Discussion (Misc queries) | |||
Finding a record based on user input | Excel Discussion (Misc queries) | |||
Positioning 2 User Forms | New Users to Excel |