Exception report in Excel

[mmarc2000]

Hello Community! I have a report building question. I have a large data table (tens of thousands of rows) where I need to test for exceptions. The data shows employee charges to our customers for each month (see example). Each employee is pre-assigned to a department and sub department. The employee needs to select the subprocess, the customer, the project, as well as other data points when entering the charge. I need to generate an "exception report" based on this employee input. For example, only the Engineering department should be charging the customer "Meta". Project "W" should only be used by the Quality Control department. The subprocess "Gray" cannot be charged to customer "P&G". Let's say there are10 different exceptions I need to test for. Using formulas or pivot tables, what are some best practices on how to handle these multiple exception queries? Note: I cannot use VBA or Power BI for this solution. Thanks!!

 

[rlv01]

I cannot use VBA or Power BI for this solution.

If so, then what Excel tools are you thinking about as far as a solution to your problem? You have described some fairly complex functionality.

 

[mmarc2000]

If so, then what Excel tools are you thinking about as far as a solution to your problem? You have described some fairly complex functionality.

Unfortunately the client is limiting me to Excel formulas or pivot tables. I am exploring the +FILTER formula and also looking at building tables of "correct data combinations" that i could lookup against. I'm open to other ideas.