Calculate if staff are still enrolled in tasks

[tigerzen]

Was trying to do this via a pivot table. Need to work out who is still enrolled in at least 1 task. There may be better ways to work this out, just after the simplest method. Will most likely need to report on the Dept status of completion but for now just after the desired result which is highlighted in yellow. Just for context, there are about 30,000 rows in the actual data with approx. 5,000 unique staff so that is why I opted for pivot tables. Can you please solve for Office 2019?

Book9
	A	B	C	D	E	F	G	H
2	Name	Task	Result	Dept		Name	Enrolled in at least 1 task	Percentage enrolled
3	Joe	A	Enrolled	Admin		Joe	Y	100%
4	Mary	B	Dropped out	Sales		Mary	Y	50%
5	Joe	B	Enrolled	Admin		Pete	N	0%
6	Pete	A	Dropped out	Finance		Sally	Y	100%
7	Sally	C	Enrolled	Sales		Harry	N	0%
8	Pete	B	Dropped out	Finance
9	Harry	A	Dropped out	Finance
10	Pete	C	Dropped out	Finance
11	Harry	B	Dropped out	Finance
12	Mary	C	Enrolled	Sales
Sheet1

 

[HighAndWilder]

Was trying to do this via a pivot table. Need to work out who is still enrolled in at least 1 task. There may be better ways to work this out, just after the simplest method. Will most likely need to report on the Dept status of completion but for now just after the desired result which is highlighted in yellow. Just for context, there are about 30,000 rows in the actual data with approx. 5,000 unique staff so that is why I opted for pivot tables. Can you please solve for Office 2019?

Book9
	A	B	C	D	E	F	G	H
2	Name	Task	Result	Dept		Name	Enrolled in at least 1 task	Percentage enrolled
3	Joe	A	Enrolled	Admin		Joe	Y	100%
4	Mary	B	Dropped out	Sales		Mary	Y	50%
5	Joe	B	Enrolled	Admin		Pete	N	0%
6	Pete	A	Dropped out	Finance		Sally	Y	100%
7	Sally	C	Enrolled	Sales		Harry	N	0%
8	Pete	B	Dropped out	Finance
9	Harry	A	Dropped out	Finance
10	Pete	C	Dropped out	Finance
11	Harry	B	Dropped out	Finance
12	Mary	C	Enrolled	Sales
Sheet1

Copy range from string until reach another string.xlsm
	A	B	C	D	E	F	G	H
1	Name	Task	Result	Dept		Name	Enrolled in at least 1 task	Percentage enrolled
2	Joe	A	Enrolled	Admin		Joe	Y	100%
3	Mary	B	Dropped out	Sales		Mary	Y	50%
4	Joe	B	Enrolled	Admin		Pete	N	0%
5	Pete	A	Dropped out	Finance		Sally	Y	100%
6	Sally	C	Enrolled	Sales		Harry	N	0%
7	Pete	B	Dropped out	Finance
8	Harry	A	Dropped out	Finance
9	Pete	C	Dropped out	Finance
10	Harry	B	Dropped out	Finance
11	Mary	C	Enrolled	Sales
Sheet30

Cell Formulas
Range	Formula
G2:G6	G2	=IF(COUNTIFS($A$2:$A$11,F2,$C$2:$C$11,"Enrolled")>0,"Y","N")
H2:H6	H2	=COUNTIFS($A$2:$A$11,$F2,$C$2:$C$11,"Enrolled")/COUNTIF($A$2:$A$11,$F2)

 

[tigerzen]

Thanks High and Wilder, you've hard-coded the names, how would you do it with a formula? This is great but as I know I'll be asked to do further analysis on this, can it be done with a pivot table?