Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Images Attached Images
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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?
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =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
  •