Return all instances if cell contains name

[tigerzen]

Book2
	A	B	C	D	E
1	Patterns	Names		Staff	Output
2	Pattern 1	Tom, Harry		Tom	Pattern 1, Pattern 2
3	Pattern 2	Jack, Sam, Tom		Harry	Pattern 1, Pattern 5
4	Pattern 3	Sara, Jane		Sara	Pattern 3, Pattern 4, Pattern 5
5	Pattern 4	Sara		Jane	Pattern 3
6	Pattern 5	Harry, Sara		Ken
Sheet1

Can I please have a solution tailored for Excel 2016 (no textjoin) for this scenario. Am after a formula/function to return the requested output in column E.

 

[alansidman]

An alternative especially if you have a lot of data
1. Load your data to Power Query.
2. Split your Names Column by delimiter comma to rows
3. Close and load your results to Data Model
4. In Power Pivot, build a measure to concatenate the patterns by name

Patterns	Names		Row Labels	Pattern
Pattern 1	Tom, Harry		Harry	Pattern 1, Pattern 5
Pattern 2	Jack, Sam, Tom		Jack	Pattern 2
Pattern 3	Sara, Jane		Jane	Pattern 3
Pattern 4	Sara		Sam	Pattern 2
Pattern 5	Harry, Sara		Sara	Pattern 5, Pattern 3, Pattern 4
			Tom	Pattern 1, Pattern 2

Power Query Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Names", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Names"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Names", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Names"}, {{"Data", each _, type table [Patterns=text, Names=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Patterns", "Index"}, {"Patterns", "Index"})
in
    #"Expanded Custom"

Power Pivot Measure to build Pivot Table

=CONCATENATEX(Table1,Table1[Patterns],", ")

 

[Peter_SSs]

Just out of interest, have you tested the formula from post #4 against your 35 rows? All of the functions used are meant to be available in 2016, and I'd be keen to know if they didn't work

Hi Kevin
I don't have 2016 to check but I thought that I had seen somewhere that CONCAT wasn't available in 2016 despite Help saying it is. Suppose it is available then I think that your approach needs amendment due to the possibility of returning incorrect results - see orange cells below that use your formula with modified sample data. The formula needs to ensure that the searched for name is not found as part of a longer name.
Also, you can shorten the formula considerably by putting the delimiter at the start of each item rather than at the end. It is simpler to eliminate the extra one from there.
I have implemented those two things in the column F formulas. Might it need Ctrl+Shift+Enter confirmation in 2016?

tigerzen.xlsm
	A	B	C	D	E	F
1	Patterns	Names		Staff	Output
2	Pattern 1	Tom, Harry		Tom	Pattern 1, Pattern 2, Pattern 6	Pattern 1, Pattern 2
3	Pattern 2	Jack, Sam, Tom		Harry	Pattern 1, Pattern 5	Pattern 1, Pattern 5
4	Pattern 3	Sara, Jane		Sara	Pattern 3, Pattern 4, Pattern 5	Pattern 3, Pattern 4, Pattern 5
5	Pattern 4	Sara		Jane	Pattern 3	Pattern 3
6	Pattern 5	Harry, Sara		Ken	Pattern 6
7	Pattern 6	Tomas, Kenrick
Sheet7

Cell Formulas
Range	Formula
E2:E6	E2	=IFERROR(LEFT(CONCAT(IF(ISNUMBER(FIND(D2,$B$2:$B$7)),$A$2:$A$7&", ","")),LEN(CONCAT(IF(ISNUMBER(FIND(D2,$B$2:$B$7)),$A$2:$A$7&", ","")))-2),"")
F2:F6	F2	=REPLACE(CONCAT(IF(ISNUMBER(FIND(", "&D2&",",", "&B$2:B$7&",")),", "&A$2:A$7,"")),1,2,"")

 

[kevin9999]

I think that your approach needs amendment due to the possibility of returning incorrect results - see orange cells below that use your formula with modified sample data. The formula needs to ensure that the searched for name is not found as part of a longer name.

Yes, all valid points Peter It would be a pity if MS's own help advice was wrong about the Concat availability (and presumably wrong for some years now...)
Cheers
Kev

 

[Alex Blakenburg]

An alternative especially if you have a lot of data
1. Load your data to Power Query.
2. Split your Names Column by delimiter comma to rows
3. Close and load your results to Data Model
4. In Power Pivot, build a measure to concatenate the patterns by name

@alansidman, I am surprised you didn't do it all in PQ

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Names", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Names.1", "Names.2", "Names.3"}),
    #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Index"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns1",{{"Value", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Value"}, {{"Concat", each _, type table [Index=number, Attribute=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Concat_Pattern", each Text.Combine([Concat][Attribute],", ")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Concat"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Concat_Pattern", type text}})
in
    #"Changed Type"

 

[alansidman]

@Alex Blakenburg

Man's mind expanded to new horizons never returns to its original dimensions.

Just expanding the OPs options.

 

[tigerzen]

Thanks so much for the wonderful replies. It's always great to have alternatives that can be tailored for different scenarios.
Kevin, I won't have access to the original data for a few days but I tried your post no. 4 solution with larger mock data and it seemed to work fine, very clever formula given the constraints of the older Excel version.
Peter the UDF works great, thank you for the suggestion and also for quality testing Kevin's suggestion, all very helpful in finding the perfect solution.
Alan and Alex, PQ was the next option if I couldn't solve with Excel functions and thanks Alex for your suggestion as I'm not very familiar with Power Pivot (get around to it someday).

 

[kevin9999]

Glad we were all able to help, and thank you so much for the kind feedback

 

[Peter_SSs]

You're welcome from me also.
I take it then that you are confirming CONCAT exists in 2016?
And if so can you confirm whether or not Kevin's formula and my alternative in column F of post #12 require array entry (Ctrl+Shift+Enter) in Excel 2016?

 

[tigerzen]

You're welcome from me also.
I take it then that you are confirming CONCAT exists in 2016?
And if so can you confirm whether or not Kevin's formula and my alternative in column F of post #12 require array entry (Ctrl+Shift+Enter) in Excel 2016?

OK, will check with my associate as I don't have 2016 either, I simply tried Kevin's solution on my computer which of course has 365 on it to see if it worked.

 

[Peter_SSs]

Cheers