Need to create formula to merge individuals in field if they share last name.

[Eurus53]

Hello all -

Here's the problem. I'm having to come up with a massive mailing list, and need to be able to create a format for the recipient name in the address field.

If two of the people in the household have the same last name, I need the formula to return the recipient names in the following format: "John & Sarah Smith".

But if the two people of the household do not share the same name, I need the formula to return the recipient names in the following format: "Nick Johnson & Katie Jewell".

Each household is a separate row. First names and last names for the two individuals are all in separate columns.

I've attached a spreadsheet (with a few examples filled in) as a sample.

Help!

E

[Ghozi Alkatiri]

try this one
=B2&IF(D2=E2,""," "&D2)&" "&"&"&" "&C2&" "&E2

[MarvinP]

Hey Eurus (again),

Put his in A2 and pull it down:

Please Login or Register  to view this content.

[Eurus53]

Thank you, this works great!

[Ghozi Alkatiri]

or try the other way
=SUBSTITUTE(CONCATENATE(B2," ",D2," ","&"," ",C2," ",E2),B2&" "&E2,B2)