# Thread: Formula to concatenate with condition

1. ## Formula to concatenate with condition

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  Reply With Quote

2. 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.  Reply With Quote

3. Wonderful NBVC. This worked like a champ. Thank You! Kudos to you!  Reply With Quote

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

5. 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.  Reply With Quote

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

#### Posting Permissions

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