Results 1 to 6 of 6

Thread: Formula to concatenate with condition

  1. #1

    Formula to concatenate with condition



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, I have the below requirement. I have columns from A to G and a 'Result' column at the end. I have total 3 rows. 1st row has states, starting with 'All' to 'NC'. If the 2nd row, under 'All' is populated with 'N', I need to find all the 'Y's in that row and print the corresponding state in 'Result' column. For ex: I need to print like this: KY;MD on the 2nd row of 'Result' column. Similarly, if the 3rd row, under 'All' is populated with 'Y', I just need to print all the states as: All;DC;IL;IN;KY;MD;NC in the 3rd row of 'Result' column. Please help.
    A B C D E F G Result
    All DC IL IN KY MD NC
    N N N N Y Y N
    Y Y Y Y Y Y Y

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Hit Alt+F11, then Insert|Module.

    Paste this function into the Editor:

    Code:
    Function aconcat(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
    Dim y As Variant
    
    If TypeOf a Is Range Then
    For Each y In a.Cells
    aconcat = aconcat & y.Value & sep
    Next y
    ElseIf IsArray(a) Then
    For Each y In a
    aconcat = aconcat & y & sep
    Next y
    Else
    aconcat = aconcat & a & sep
    End If
    
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
    End Function
    then close and go back to the sheet. In the Result column enter formula:

    =SUBSTITUTE(TRIM(aconcat(IF(A2:G2="Y",A$1:G$1,"")," "))," ",";")

    Confirm the formula with CTRL+SHIFT+ENTER and not just ENTER. You should see { } brackets appear around the formula. Then copy down.


  3. #3
    Wonderful NBVC. This worked like a champ. Thank You! Kudos to you!

  4. #4
    Just curious to know is it possible to use a direct formulae without the above macros?

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Yes you can, but if you have a lot of columns then it becomes a bit cumbersome....

    for you sample, try:

    =SUBSTITUTE(TRIM(IF(A2="Y",A$1,"")&" "&IF(B2="Y",$B$1,"")&" "&IF(C2="Y",$C$1,"")&" "&IF(D2="Y",$D$1,"")&" "&IF(E2="Y",$E$1,"")&" "&IF(F2="Y",$F$1,"")&" "&IF(G2="Y",$G$1,""))," ",";")

    copied down.


  6. #6
    You are the best. This works too. Thank You for your time and help! Appreciate it.

Posting Permissions

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