# Thread: Concatenate multiple results into single cells based on criteria check

1. ## Concatenate multiple results into single cells based on criteria check

I have two columns in Workbook1 - "Issues" and "Regions":
 Issues Regions 4455 Atlantic 2555 Central 7777 Midwestern 3333 Central 4444 Central 6333 Midwestern

I like to list all Issues according to Regions with their corresponding Issues in second Workbook or Excel File such as below:

 Regions Issues Atlantic 4455 Central 2555, 3333, and 4444 Midwestern 7777 and 6333

Could you show me a formula to do this and how? Thanks.

2. You will need to use a user-defined function to achieve this if you want the values in single cells....

Add this UDF in a new VBA Module (ALT+F11 >> Insert >> Module)

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 assuming your data is in Sheet1, A1:B10, and your lookup values are in Sheet2, starting in A2, then in B2 enter formula:

=SUBSTITUTE(TRIM(aconcat(IF(Sheet1!\$B\$2:\$B\$10=A2,Sheet1!\$A\$2:\$A\$10,"")," "))," ",",")

confirmed with CTRL+SHIFT+ENTER and not just ENTER, then copy down.

3. What is "y"?

4. My first excel file is in this path "C:\Desktop\Issue Report 1.xls". How do I put that in the SUBSTITUTE? Thanks.

5. the "y" is just a variable used in the UDF to hold the cell values in.

try formula:

=SUBSTITUTE(TRIM(aconcat(IF('C:\Desktop\[time example.xlsx]Sheet1'!\$B\$2:\$B\$10=A2,'C:\Desktop\[time example.xlsx]Sheet1'!\$A\$2:\$A\$10,"")," "))," ",",")

assuming your sheetname at that path is Sheet1.

6. Okay, I tried this:

Sheet 1 is in my first excel file worksheet, Region is my second excel file worksheet.

=SUBSTITUTE(TRIM(aconcat(IF([Sheet]Sheet1!\$B\$2:\$B\$10=A2,Region!\$A\$2:\$A\$10,"")," "))," ",",")

I'm rather getting "Atlantic, Central,Midwest" concatenated together. I'm actually looking to concatenate all Issues by regions rather. e.g. Central "2222, 3333". How do I correct the formula? Thanks.

7. You also need to replace the Region! part with [Sheet]Sheet1!

=SUBSTITUTE(TRIM(aconcat(IF([Sheet]Sheet1!\$B\$2:\$B\$10=A2,[Sheet]Sheet1!\$A\$2:\$A\$10,"")," "))," ",",")

8. Woww...yes, the issues numbers now show up..however, they're all showing up and point to one region...e.g. Atlantic 1111, 2222, 3333, 4444, 5555, and 6666.

So in my second excel sheet i prepared column A, formula is in b2:

Atlantic 1111, 2222, 3333, 4444, 5555, 6666
central
Midwest

How can I just filter out by regions with their corresponding Issue Numbers?

9. Did you confirm with CTRL+SHIFT+ENTER?

10. I might not have..press all three keys together? And do I re-enter the formula?

Page 1 of 2 1 2 Last

#### Posting Permissions

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