Greetings,
I need help creating formulas for an inventory reimbursement report so I can determine if any inventory units that qualify for reimbursement have not been automatically reimbursed and/or if the reimbursement ammount received has any calculation errors. I'm including a sample of the excel report I'm working on which includes an image of short hand codes to define the column headings.
Note, the CIR3 report contains all of the units that have automatically been reimbursed, and I need to check them (by looking up the FNSKU) against the EDQM report which shows all units (usually multiple rows of the same FNSKU) that qualify for reimbursement. If the values for a unit in CIR3 match in EDQM, I want to color code the row in both reports so I know it's correct in the CIR3 report, and so I know not to include that unit in the EDQM report in a batch of units that need to be reimbursed.
If the values for a unit in CIR3 do not match with the same unit in EDQM, I want excel to calculate the differences and enter selected field values into a discrepancy table so I can create cases to correct the reimbursement amounts.
For clarification (hopefully I haven't been too confusing already)
What Needs to be Determined:
When an FNSKU is present in both CIR3 and EDQM reports:
IS: The CIR3 report [QRC, QRN, TURMB, & AMTL] respective field values < or = to the respective field values in the EDQM report?
IF: CIR3 respective field values are = to the respective field values in EDQM, highlight FNSKU rows in both reports with reconciled color code.
IF: CIR3 respective field values are < respective field values in EDQM, calculate differences for each field value and highlight FNSKU rows in each report with correction needed color code and enter calculated values [QRC, QRN, TURMB, & AMTL] along with corresponding [FNSKU, SKU, REASON, ITEM, PPU] fields into the discrepancy table for case log.
RECONCILEDColor Code - Blue, Accent 1, Lighter 40%
CORRECTION NEEDEDColor Code - Custom Red RGB (255, 80, 80)
Hopefully someone here can help me. I appreciate any help and recommendations.
Many Thanks!