Text Question

[John Holder]

I have columns of text (First Name and Last Name) and wish
to do three things.

In a third column I want to display the first charcter of
of the First Name and the entire last name. (ie A1
is "John", B1 is "Holder", C1 "jholder")

In a forth column I want to display the first and last
characters of the first name plus the first and last
characters of the last name. (ie A1 is "John",
B1 "Holder", D1 is "jnhr")

Lastly, I need to make sure that nothing in column C or D
repeats.

Will excel do all this?

Thanks,

John Holder

 

[Andy B]

John

Put this in C2
=LEFT(A2,1)&B2

This in D2
=LEFT(A2,1)&RIGHT(A2,1)&LEFT(B2,1)&RIGHT(B2,1)

And this in a helper colum E2
=IF(COUNTA(C2,C2:C1000)>1,"MULTIPLE","SINGLE")
to show multiple entries

Andy

 

[Dan E]

For jholder use =LOWER(LEFT(A1,1) & B1)
for jnhr use=LOWER(LEFT(A2,1) & RIGHT(A2,1) & LEFT(B2,1) & RIGHT(B2,1))
In E1 put =COUNTIF($C$1:$C$100,$C1) and drag down, this will count the
occurence of jholder in the list if you get a 2 their are repeats
In F1 put =COUNTIF($D$1:$D$100,$D1) again counts the occurences of jnhr so a
2 means repeats
I used 1 to 100 as an example change it to suit your data

Dan E