Hello, im hitting a bit of a brain fart.
We have a data set, and are looking to build a report.
There is a total of 6 classes we have delivered, and we want to do an audit on our learners. Each class has prerequisites. Our data set captures each person attending the class by class name, Date of completion, and a "Green" or "Red" status for completing the class and their pre-requisites.
Im attempting to create a function or formula with student ID's for my Rows, then having the 5 modules in the columns. Those Columns would have a date and a Yes or No for obtaining a Green or Red Status.
So what i was trying to do is build a formula to lookup their strudent ID (say its B4) and look through the raw data, if it finds the same ID, does another Column have the Course name? If it does, preform a validation on the "Red" "Green" column and if it is Green = True return a Yes in the cell. Similarly preform the same task but return the Date the class was taken regardless of status (Red/Green column).
So you would figure it would be two simple formulas, but im struggling to build it with the best function. The raw data has ALOT. And some student ID's have completed multiple classes. If this can be done in a formula without extracting the data, and then combining them into this dashboard, that would be best, but if there is a formula i can base it off of, i can probably make it work, just need some help on the types of formulas i would use with this.
Thanks in advance!
