How to display all staff/manager name according to similar project, but on different task?

[haha97]

Hi. Previously, I have posted this question How to split the name and display the status of task/project for each name?. Usually, each staff has their own task for the similar project. I tried to add additional column, which is Task and the output only recognize the first and second name of staff (M1 and M2), but for M3 and M4, it can't recognized as shown in table below. The status project for M2 should be in progress instead of on-hold. Kindly need your help for this question and thank you.

project_tracker.xlsx
	A	B	C	D	E	F	G	H	I
1								Date	4/4/2022
2	Date1	Project	Task	status	manager			Project	A
3	4/4/2022	A	Task1	in progress	M1/M2			Manager	Status Project
4	4/4/2022	A	Task2	on hold	M3/M4			M1	in progress
5	4/5/2022	B	Task3	in progress	M3/M2			M2	on hold
6	4/6/2022	C	Task4	other	M4/M5/M6
Sheet4

Cell Formulas
Range	Formula
H4:H6	H4	=IFNA(TRIM(MID(SUBSTITUTE(VLOOKUP(I$2,B$3:E$6,4,0),"/",REPT(" ",100)),ROWS(H$4:H4)*100-99,100)),"")
I4:I6	I4	=IFNA(IF(H4="","",VLOOKUP(I$2,B3:E6,3,0)),"")

Desired output:

project_tracker.xlsx
	H	I
10	Date	4/4/2022
11	Project	A
12	Manager	Status Project
13	M1	in progress
14	M2	in progress
15	M3	on hold
16	M4	on hold
Sheet4

 

[JamesCanale]

Here's something that will work in 365, but it's not great.

MrExcelPlayground7.xlsx
	A	B	C	D	E	F	G	H	I
1								Date	4/4/2022
2	Date1	Project	Task	status	manager			Project	A
3	4/4/2022	A	Task1	in progress	M1/M2			Manager	Status Project
4	4/4/2022	A	Task2	on hold	M3/M4			M1	in progress
5	4/5/2022	C	Task3	in progress	M3/M2			M2	in progress
6	4/6/2022	C	Task4	other	M4/M5/M6			M3	on hold
7								M4	on hold
8
9
10
11
Sheet27

Cell Formulas
Range	Formula
H4:H7	H4	=SORT(FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN("/",TRUE,FILTER(E3:E6,B3:B6=I2)),"/","</y><y>")&"</y></x>","//y"))
I4:I11	I4	=IF(H4<>"",INDEX(FILTER($D$3:$D$6,$B$3:$B$6=$I$2),MAX(--(NOT(ISERR(SEARCH(H4,FILTER($E$3:$E$6,$B$3:$B$6=$I$2)))))*SEQUENCE(ROWS(FILTER($E$3:$E$6,$B$3:$B$6=$I$2))))),"")
Dynamic array formulas.

 

[haha97]

Here's something that will work in 365, but it's not great.

MrExcelPlayground7.xlsx
	A	B	C	D	E	F	G	H	I
1								Date	4/4/2022
2	Date1	Project	Task	status	manager			Project	A
3	4/4/2022	A	Task1	in progress	M1/M2			Manager	Status Project
4	4/4/2022	A	Task2	on hold	M3/M4			M1	in progress
5	4/5/2022	C	Task3	in progress	M3/M2			M2	in progress
6	4/6/2022	C	Task4	other	M4/M5/M6			M3	on hold
7								M4	on hold
8
9
10
11
Sheet27

Cell Formulas
Range	Formula
H4:H7	H4	=SORT(FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN("/",TRUE,FILTER(E3:E6,B3:B6=I2)),"/","</y><y>")&"</y></x>","//y"))
I4:I11	I4	=IF(H4<>"",INDEX(FILTER($D$3:$D$6,$B$3:$B$6=$I$2),MAX(--(NOT(ISERR(SEARCH(H4,FILTER($E$3:$E$6,$B$3:$B$6=$I$2)))))*SEQUENCE(ROWS(FILTER($E$3:$E$6,$B$3:$B$6=$I$2))))),"")
Dynamic array formulas.

Thank you for your reply. Actually, I used my office laptop and it only supported Microsoft 2019 only. I know that FILTERXML is only supported for Microsoft 365 and 2021

 

[haha97]

Here's something that will work in 365, but it's not great.

MrExcelPlayground7.xlsx
	A	B	C	D	E	F	G	H	I
1								Date	4/4/2022
2	Date1	Project	Task	status	manager			Project	A
3	4/4/2022	A	Task1	in progress	M1/M2			Manager	Status Project
4	4/4/2022	A	Task2	on hold	M3/M4			M1	in progress
5	4/5/2022	C	Task3	in progress	M3/M2			M2	in progress
6	4/6/2022	C	Task4	other	M4/M5/M6			M3	on hold
7								M4	on hold
8
9
10
11
Sheet27

Cell Formulas
Range	Formula
H4:H7	H4	=SORT(FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN("/",TRUE,FILTER(E3:E6,B3:B6=I2)),"/","</y><y>")&"</y></x>","//y"))
I4:I11	I4	=IF(H4<>"",INDEX(FILTER($D$3:$D$6,$B$3:$B$6=$I$2),MAX(--(NOT(ISERR(SEARCH(H4,FILTER($E$3:$E$6,$B$3:$B$6=$I$2)))))*SEQUENCE(ROWS(FILTER($E$3:$E$6,$B$3:$B$6=$I$2))))),"")
Dynamic array formulas.

Is there any solution that you can recommend to me? Thank you

 

[JamesCanale]

filterxml works on older excels. The textjoin, sort and filter doesn't work on older excels... I don't think I could pull this off in older excel.

 

[haha97]

filterxml works on older excels. The textjoin, sort and filter doesn't work on older excels... I don't think I could pull this off in older excel.

Yes. I have tried it, but it stated that 'the function isn't valid'.