Feb 19 2020 04:21 AM
Hi
I am sure that there should be a really easy solution to this, but I have been going around in circles trying various different formulas and pivot tables.
Currently I have a data download that shows every student, and what they have been attending each week.
What I need to be able to do is look up the module name, the activity and count the number of "Y" for each student over the total number of weeks, so that I can have an overview/summary of how many have attended.
I have attached a quick snapshot book, but the data it's self is about 10,000 rows.
Thanks
Machala
Feb 19 2020 04:52 AM
Solution
I don't know if I understood properly but please check attached file and see if it helps.
Your original data was not suitable for Pivot table so I used PowerQuery to unpivot it (check UNPIVOTED DATA sheet) and then generated from it a Pivot table that may help you.
If you check filter field in the PIvot you will see that you can filter between "Y", "N" depending on what you need to count.
BR,
Branislav
Feb 19 2020 05:54 AM
Hi Branislav
Thanks for your reply, data sort of works, apologies for not making it clearer. I am trying to add up the total number of "Y" for each activity and module for example if 2 students attended the lecture for archery that would be 2, but if 3 attended the tutorial for archery this would be 3
Thanks
Machala
Feb 19 2020 05:58 AM
Feb 19 2020 06:02 AM
Hi Branislav
You are a superstar, thanks for saving my sanity
Kind regards
Machala
Feb 19 2020 04:52 AM
Solution
I don't know if I understood properly but please check attached file and see if it helps.
Your original data was not suitable for Pivot table so I used PowerQuery to unpivot it (check UNPIVOTED DATA sheet) and then generated from it a Pivot table that may help you.
If you check filter field in the PIvot you will see that you can filter between "Y", "N" depending on what you need to count.
BR,
Branislav