Hello,

I have a report which contains a daily breakdown of exceptions, with a total time for each exception, that occurred throughout the day. The date range of the report is variable, there are a total of 20 potential exceptions, the number of exceptions per day is variable and there can only be one of any exception for the day.

I've attached a workbook to show what I've worked on so far:

- Columns A:D is the format in which the reporting is available
- Columns F:G, I:J, L:M present data from report in daily breakdown
- Columns O:P, R:S, U:V, etc. (for all days of the year) is the way I wish to finally present the data

How can I go from the raw report data (columns A:D), where the exceptions and number of exceptions change per day, to a presentable format where all potential exceptions are listed by day with totals for those exceptions?

I want to have the ability to process a report with an entire year date range which would mean the report could be up to 7300 rows (20 exceptions * 365 days). As you can see with my current design, I require an "exception" and "time" column for every date (columns F:G, I:J, L:M, etc.) which would, ultimately, mean my sheet would have to accommodate up to 730 columns (365 * 2 columns) * 7300 rows (5,329,000 cells with formulas) which is not really feasible.

One thought I had but I'm uncertain what to search for (or if it's possible): is there any way for the formulas in columns F:G, I:J, L:M to search through the entire raw report data and list the exceptions and times starting at the top, or is there no way to get around it needing to look through in sequence, filling in unmatched cells with 0's?

Many thanks!

Exceptions & times by date.xlsx