Concatenate multiple results into single cells based on criteria check

E

Excel Learner 77

New member
Joined
Aug 29, 2014
Messages
13
Reaction score
0
Points
0
I have two columns in Workbook1 - "Issues" and "Regions":
IssuesRegions
4455Atlantic
2555Central
7777Midwestern
3333Central
4444Central
6333Midwestern


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

RegionsIssues
Atlantic4455
Central2555, 3333, and 4444
Midwestern7777 and 6333


Could you show me a formula to do this and how? Thanks.
 
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.
 
My first excel file is in this path "C:\Desktop\Issue Report 1.xls". How do I put that in the SUBSTITUTE? Thanks.
 
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.
 
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.
 
Last edited:
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,"")," "))," ",",")
 
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?
 
Did you confirm with CTRL+SHIFT+ENTER?
 
Just go into B2 (or wherever you entered the formula), then hit F2 to activate the cell, press all 3 keys at once. You should see { } brackets appear around the formula. Then you can copy down.
 
It should, if you the ranges in your formula covers that new data
 
I added data within the range. The formula exists in the cell already..just press ctrl + shift + enter?
 
Ok this is working so awesome...I can add as much data now as I want, and the filtering still works. Thanks again!!!!
 
You must log in or register to reply here.
Back
Top