Simple Excel attendance report formatting challenge

[Ditto]

I have a simple Y/N attendance data in a format as follows:

 

I want to reformat to show as follows:

 

Ideally, slicers or selection filters on "Attended" and "Subject" and grouping by "Subject". I've got close with a Pivot, close with a Powerquery (making use of Transform -> Pivot Column), but haven't got something quite as I'd like. Once working, I will be looking to implement a one-click macro solution against the underlying source dataset.

[Ditto]

Sample file for those up for the challenge.

Attendance.xlsx

[Ditto]

I'm going to see if a pure VLOOKUP solution can be found today. Failing the powers of Edugeek coming up with a neat solution, are there any Excel specific forums anyone would recommend posting this challenge in.

[howartp]

I'm following with interest; i'm sure it can be done and I'd certainly be up for it, but haven't time to play at the moment.

[Seb1780]

Like this?

Attendance.xlsx

[howartp]

That's nice and succinct.