We have a 3-stage invoice clearing process. Have a look at the below screenshot to understand how the data is captured in the invoice status excel report.
Based on words mentioned under each stage, the invoice status is categorized into two main categories. They are CLEARED and PENDING UNDER STAGE 1 / STAGE 2 / STAGE 3.
Criteria to decide a invoice status category is listed below:
PENDING -> If any of the stage contains the word PENDING, the status should be mentioned as PENDING UNDER STAGE <stage number should be mentioned like STAGE 1 / STAGE 2 / STAGE 3)
CLEARED -> If all the stage has the word CLEARED, the status should be mentioned as CLEARED.
We need to use a simplest formula to derive to this solution. Result should appear like the screenshot given below (it is just a sample screenshot; results might differ in the excel given for downloading)
Download the excel file by clicking here
You can post your solution in the comments section