Concatenate / Array Help

Hi there,
I wondered if someone could help with something I'm trying to do.
I'm trying to remove duplicates from a horizontal list whilst also putting commas in between each unique value and having an "&" sign before the last place.
I have attached a copy of what I'm trying to do and the column in yellow shows what I am after from each row, I was wondering if this was possible?
Some rows will have only 1 entry, and some will have up to 6.
Any questions, just shout.
Thanks so much.
SamHelp.xlsx

Hi,

Try the following formula in G2:

=SUBSTITUTE(A2&", "&IF(A2=B2,"",B2)&", "&IF(OR(B2=C2,A2=C2),"",C2)&", "&IF(OR(C2=D2,B2=D2,A2=D2),"",D2)&", "&IF(OR(D2=E2,C2=E2,B2=E2,A2=E2),"",E2)&", "&IF(OR(E2=F2,D2=F2,C2=F2,B2=F2,A2=F2),"",F2)," ,","")

Thank you very much for your reply.
Do you know if there is anyway for an "&" sign go before the last location and to remove the "," at the end?

Hi,

Sorry for the late response! See the attached file, the formula is a bit lengthy though.

Please Login or Register  to view this content.

No problem for the delay, thanks for getting back to me. From what I can see the formula you have designed has successfully removed the last "," but it doesn't include the "&" before the last entry (when more than 2 entries).
e.g. I'm after something which returns this for the first row...

Bluewater - Wintergarden, Berners Street, Shrewsbury & Nottingham - Market Square

If there is only one entry then I don't need any "," or "&"

No worries if this isn't possible, I can use what you have done and it will be very helpful however if you could add this last bit that would be ace.

Thanks

Hi,

See the attached file, I have used the following formula in H2 to get the desired output:

=IFERROR(REPLACE(G2,FIND("@",SUBSTITUTE(G2,",","@",(LEN(G2)-LEN(SUBSTITUTE(G2,",","")))/LEN(","))),1," &"),G2)