How to split the name and display the status of task/project for each name?

[haha97]

Hi. I want to create a summary report. The table below (at the left side) show that project A is in progress and managed by M1 and M2 (need to key in by staff).
To create a summary report (table at the right side), I use INDEX and MATCH (with the help from helper column). But the problem is, it cannot separate the manager name to each row. Is there a way on how I can split M1/M2, each name to summary report and display the status for each. Thank you.

project_tracker.xlsx
	A		C			F	G	H	I	J	K	L
5											Date	4/4/2022
6	Date1		Project			status	manager	Helper			Project	A
7	4/4/2022		A			in progress	M1/M2	44655AM1/M2			Manager	Status Project
8											M1	-
9											M2	-
Sheet1

Cell Formulas
Range	Formula
H7	H7	=A7&C7&G7
L8:L9	L8	=IFNA(INDEX(F:F,MATCH(L$5&L$6&K8,H:H,0)),"-")

Cells with Data Validation
Cell	Allow	Criteria
L6	Any value

Desired output:

project_tracker.xlsx
	A		C			F	G	H	I	J	K	L
5											Date	4/4/2022
6	Date1		Project			status	manager	Helper			Project	A
7	4/4/2022		A			in progress	M1/M2	44655AM1/M2			Manager	Status Project
8											M1	in progress
9											M2	in progress
Sheet1

Cell Formulas
Range	Formula
H7	H7	=A7&C7&G7

Cells with Data Validation
Cell	Allow	Criteria
L6	Any value

 

[mumps]

Does L6 contain a drop down with the names of all projects? Will there always be 2 managers for each project?

 

[haha97]

Does L6 contain a drop down with the names of all projects? Will there always be 2 managers for each project?

No, L6 will not contain dropdown, it is fixed. For each project, sometimes, there will be 1 until 5 manager, who will handle the project.

 

[lrobbo314]

This seems to do what you are looking for.

Book1
	A	B	C	D	E	F	G	H
1							Date	4/6/2022
2	Date1	Project	status	manager			Project	C
3	4/4/2022	A	in progress	M1/M2			Manager	Status Project
4	4/5/2022	B	in progress	M3			M4	other
5	4/6/2022	C	other	M4/M5/M6			M5	other
6							M6	other
Sheet2

Cell Formulas
Range	Formula
G4:G6	G4	=FILTERXML("<a><b>" & SUBSTITUTE(INDEX($D$3:$D$5,MATCH($H$1&$H$2,$A$3:$A$5&$B$3:$B$5,0)),"/","</b><b>") & "</b></a>","//b")
H4:H6	H4	=LET(m,INDEX($C$3:$C$5,MATCH($H$1&$H$2,$A$3:$A$5&$B$3:$B$5,0)),o,OFFSET(m,,1),INDEX(m,SEQUENCE(LEN(o)-LEN(SUBSTITUTE(o,"/",""))+1,,,0)))
Dynamic array formulas.

 

[haha97]

This seems to do what you are looking for.

Book1
	A	B	C	D	E	F	G	H
1							Date	4/6/2022
2	Date1	Project	status	manager			Project	C
3	4/4/2022	A	in progress	M1/M2			Manager	Status Project
4	4/5/2022	B	in progress	M3			M4	other
5	4/6/2022	C	other	M4/M5/M6			M5	other
6							M6	other
Sheet2

Cell Formulas
Range	Formula
G4:G6	G4	=FILTERXML("<a><b>" & SUBSTITUTE(INDEX($D$3:$D$5,MATCH($H$1&$H$2,$A$3:$A$5&$B$3:$B$5,0)),"/","</b><b>") & "</b></a>","//b")
H4:H6	H4	=LET(m,INDEX($C$3:$C$5,MATCH($H$1&$H$2,$A$3:$A$5&$B$3:$B$5,0)),o,OFFSET(m,,1),INDEX(m,SEQUENCE(LEN(o)-LEN(SUBSTITUTE(o,"/",""))+1,,,0)))
Dynamic array formulas.

Hi, why I got error #VALUE on G4 and #NAME in H4?

 

[Fluff]

What version of Excel are you trying this in?

 

[haha97]

What version of Excel are you trying this in?

Excel 2019

 

[Fluff]

The formula in H4 will only work in 365 or 2021. And the formula in G4 will need array entry, although it won't give the results your after.

 

[haha97]

The formula in H4 will only work in 365 or 2021. And the formula in G4 will need array entry, although it won't give the results your after.

Is there a way to handle this. I mean, instead of using filterxml and other function, is there a possibility to provide another solution? Thank you.

 

[Fluff]

According to your profile you have 365 so why not use that?