I've done a lot of similar work. I cannot say how exactly I'd approach this task before I see your file and data I'd have to work with. Maybe a simpler approach using formulas would suffice, maybe it won't, then I'll use VBA. If you plan to tweak or upgrade this solution in the future, then I think it's better to use VBA from the start because VBA is a very flexible tool with almost unlimited capabilities while formulas have a limited scope of applicability. You don't want a situation when you get a quick solution using formulas but then when you decide to improve it you find out that it reached its limits, so you have to scrap it and build a new VBA solution from scratch. Anyway, like I said, please let me see your data and then I'll tell you how I'd solve the problem.
I'm a computer science professional with extensive experience in automation of data acquisition, processing, analysis, and presentation in Excel using custom dashboards, macros (VBA scripts), pivots, filters, and other advanced tools. Please have a look at some Excel projects I've done:
https://www.freelancer.com/projects/Excel/Calculate-the-semi-deviation-each/
https://www.freelancer.com/projects/Excel/Recreate-online-calculator-using-Excel/
https://www.freelancer.com/projects/Excel/Build-Excel-reports-dashboard/
https://www.freelancer.com/jobs/php/Need-excel-plugin-convert-USD/
https://www.freelancer.com/jobs/Excel/Need-covert-very-large-dat/