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.

 

[Phuoc]

Try this in E2:

=TEXTJOIN(",",1,IF(ISNUMBER(SEARCH(D2,$B$2:$B$6)),$A$2:$A$6,""))

 

[tigerzen]

Thanks Phuoc, I need a solution without Textjoin.

Try this in E2:

=TEXTJOIN(",",1,IF(ISNUMBER(SEARCH(D2,$B$2:$B$6)),$A$2:$A$6,""))

 

[kevin9999]

Until a more elegant solution comes along...

Book1
	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

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

 

[Peter_SSs]

Am after a formula/function to return the requested output

Can it be a user-defined function using vba?
If not how many rows do you actually have (or could you actually have) in columns A:B in your real data?

 

[tigerzen]

Can it be a user-defined function using vba?
If not how many rows do you actually have (or could you actually have) in columns A:B in your real data?

UDF is okay if we can't find an easier formula, Col A around 35 rows, Col B around 5 rows.

 

[Peter_SSs]

Col A around 35 rows, Col B around 5 rows.

Don't column A and column B have equal number of rows of data?
Did you mean column D instead of column B?

 

[tigerzen]

Don't column A and column B have equal number of rows of data?
Did you mean column D instead of column B?

I may have oversimplified this, the actual data in cold A & B have approx 35 rows.

 

[Peter_SSs]

the actual data in cold A & B have approx 35 rows.

In that case a worksheet formula solution for Excel 2016 is not feasible in my view.
You could instead try this user-defined function.

Function StaffPatterns(rData As Range, sStaff As String) As String
  StaffPatterns = Join(Filter(Split(Join(Filter(Application.Transpose(Evaluate(rData.Columns(1).Address & "&""@|, ""&" _
      & rData.Columns(2).Address & "&"",""")), ", " & sStaff & ","), "@"), "@"), "|", False), ", ")
End Function

tigerzen.xlsm
	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

Cell Formulas
Range	Formula
E2:E6	E2	=StaffPatterns(A$2:B$6,D2)

 

[kevin9999]

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