Excel 2007 : If cells are unequal, then concatenate

**abanford** · 04-23-2010, 11:41 AM

Happy Friday!

I am looking for a formula that will concatenate email addresses in cells if they are not the same. I'd rather not have duplicates of the same addresses in the result.

There's a spreadsheet attached that shows what I am looking for better than I am describing.

Thanks in advance for your help!
Alyssa

**DonkeyOte** · 04-23-2010, 11:50 AM

one possibility might be:

G2:
=REPT(A2&";",A2<>"")&REPT(B2&";",AND(B2<>"",COUNTIF($A2:B2,B2)=1))&REPT(C2&";",AND(C2<>"",COUNTIF($A2:C2,C2)=1))&REPT(D2&";",AND(D2<>"",COUNTIF($A2:D2,D2)=1))&REPT(E2&";",AND(E2<>"",COUNTIF($A2:E2,E2)=1))&REPT(F2&";",AND(F2<>"",COUNTIF($A2:F2,F2)=1))
copied down

(note I used a semi-colon delimiter)

**darthux** · 04-26-2010, 07:56 PM

you would type in cell G8:

"=CONCATENATE(IF(A8=B8,A8,(CONCATENATE(A8,", ",B8))),(IF(C8=D8,D8,(CONCATENATE(C8,", ",D8)))),", ",(CONCATENATE(IF(E8=F8,E8,(CONCATENATE(E8,", ",F8))))))"

depending if you have blank cells, then you could copy and paste values to remove the extra ", "

see your attachment with results