Concatenate Unique

Topic

New Reply

Hi

I need to concatenate IF there is a value and if the value isn’t already in the other cells

The formula is not working as expected
=SUBSTITUTE(TRIM(CONCATENATE(A2,” “,B2,” “,C2,” “,D2,” “,E2)),” “,”, “)

see the result that I am looking for in the attached, what formula should be able to return this

TIA

Reply | Quote

Replies

How about:
=A2&IF(OR(B2=””,ISNUMBER(MATCH(B2,$A2:A2,0))),””,”,”&B2)&IF(OR(C2=””,ISNUMBER(MATCH(C2,$A2:B2,0))),””,”,”&C2)&IF(OR(D2=””,ISNUMBER(MATCH(D2,$A2:C2,0))),””,”,”&D2)&IF(OR(E2=””,ISNUMBER(MATCH(E2,$A2:D2,0))),””,”,”&E2)

Steve

Reply | Quote

How about:
=A2&IF(OR(B2=””,ISNUMBER(MATCH(B2,$A2:A2,0))),””,”,”&B2)&IF(OR(C2=””,ISNUMBER(MATCH(C2,$A2:B2,0))),””,”,”&C2)&IF(OR(D2=””,ISNUMBER(MATCH(D2,$A2:C2,0))),””,”,”&D2)&IF(OR(E2=””,ISNUMBER(MATCH(E2,$A2:D2,0))),””,”,”&E2)

Steve

Hi Steve

Thank, its work beautifully. How does this formula works?

Reply | Quote

=A2&IF(OR(B2=””,ISNUMBER(MATCH(B2,$A2:A2,0))),””,”,”&B2)&IF(OR(C2=””,ISNUMBER(MATCH(C2,$A2:B2,0))),””,”,”&C2)&IF(OR(D2=””,ISNUMBER(MATCH(D2,$A2:C2,0))),””,”,”&D2)&IF(OR(E2=””,ISNUMBER(MATCH(E2,$A2:D2,0))),””,”,”&E2)

Breaking it apart:
=A2
assumes that something will be in Col A so gets it

&IF(OR(B2=””,ISNUMBER(MATCH(B2,$A2:A2,0))),””,”,”&B2)

The MATCH(B2,$A2:A2,0) checks to see if B2 is in A2. If it is it will be a number so ISNUMBER(MATCH(B2,$A2:A2,0))) would be true.

Thus this checks column B to see if it is blank (=””) OR if it finds a MATCH. If either is true it just concatenates a null string (”), if it has something that is not a match it puts a comma and the value

Similarly:
IF(OR(C2=””,ISNUMBER(MATCH(C2,$A2:B2,0))),””,”,”&C2) checks column c item for blank or a match in A:B
IF(OR(D2=””,ISNUMBER(MATCH(D2,$A2:C2,0))),””,”,”&D2) checks column d item for blank or a match in A:C
IF(OR(E2=””,ISNUMBER(MATCH(E2,$A2:D2,0))),””,”,”&E2) checks column e item for blank or a match in A:D

At the end if any column is a blank or a match, it adds nothing, but if they are not it adds a comma and the value from that column.

Steve

Reply | Quote