1. 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

=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,\$A22,0))),"",","&E2)

Steve

3. [quote name='sdckapr' post='765669' date='17-Mar-2009 03:07']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,\$A22,0))),"",","&E2)

Steve[/quote]

Hi Steve

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

4. =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,\$A22,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))),"",","&C 2) checks column c item for blank or a match in A:B
IF(OR(D2="",ISNUMBER(MATCH(D2,\$A2:C2,0))),"",","&D 2) checks column d item for blank or a match in A:C
IF(OR(E2="",ISNUMBER(MATCH(E2,\$A22,0))),"",","&E2) checks column e item for blank or a match in A

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•