[ms office - 2016] Excel Unique Value

[sefton]

I've been asked to produce an automated report from Sims that gives our schools exclusion data for the Trust KPI but I'm really struggling with one aspect. I've designed the report to run straight from Sims and output in the attached sheet

 

It exports based on date filters in Sims for that academic year and then gives secondary figures based on date fields that can be changed by the user in the report. I then need to count the number of students who have more than one exclusion with the variables of whether they were PP and then the date range set by the user. For the complete data set I should be able to do a macro that uses Advanced Filter and then counta but I'm really stuck on how to count those students who have more than one exclusion in the date range set in D2 and D3.

Anonymous.xlsx

[Bedders]

You could possibly do this with a hidden boolean field (TRUE/FALSE) for whether they're in the date range, which may simplify lookups.

Then some kind of pivot table with that as a filter, and the Count of StudentID maybe? I don't often get to play in Excel but that might give you some other ideas. Good luck

 

ETA: You can count unique values in a pivot table https://www.contextures.com/pivottablecountunique.html That only gives the number of people who were in inclusion rather than specifically who though...

Edited October 21, 2019 by Bedders

[sefton]

Thanks, that worked for the YTD figures, just got to work out how to use the dates preciously inputted by the end user to produce the date restricted unique value outputs.