Solved: Lookup and use left characters

[wilg]

Hi, I have a lenghty list of first names in column A and last names in column C. In column A I have the formula

=TRIM(B1&" "&LEFT(C1,2))

This gives me as example

John Do

I need this to differientiate the person if there is more than one John. What I want is to...

1. Only use the formula if lookup finds more than one John as eg..

2. If it finds more than one John D, then continue with the next character to differentiat between them. EG. John Doe, John Doea may need 4 characters to tell the diff.

All help is appreciated thanks...

[Aflatoon]

If you had for example:
John Smith
John Doe
John Doea

would you want returned:
John
John D
John Do

or:

John
John Doe
John Doea

[wilg]

Hi thanks for your help..

I would like it to return

John S
John Doe
John Doea

[Aflatoon]

This seems to work (albeit very limited testing) using helper columns.

[wilg]

Great solution. Thanks very much.