I am working on simplifying our employee benefit reconcile process at work. With over 500 employees and 16 different enrollment options, the current reconcile process is messy and prone to errors.
I've created a flow in Power Automate Desktop (PAD) that allows the user to upload a payroll report and carrier invoice. The flow cleans up the data from the two files and writes and saves it to an Excel Workbook. I plan to use PAD to manipulate Macro Enabled Worksheet and heavily reduce the amount of user input.
That being said, the workbook has two sheets, a payroll report and carrier invoice.
I am wanting to use VBA/Macro to compare both worksheets and export the variances to a template I built. I know there are a ton of posts on comparing two sheets, but I am needing the VBA to look at several columns to make comparisons.
For instance, an employee may be listed twice on the payroll report. Once for their health premium and once for their dental. The same can be said about the carrier invoice.
I need the VBA to look at the "Employee ID" column between both sheet and then look at the "Product Column" to see if it's for health or dental. Once it matches those two criteria, I need it to look at the "Cost" to see if there is a variance.
There should only be 3 things that would cause an employee to be on the variance sheet:
- The employee is on the payroll report but not on the invoice.
- The employee is on the invoice but not on the payroll report.
- There is a difference in what payroll deduction amount and what was on the carrier invoice. (Ideally, I would have it only show differences that were greater than$1.00)
I've attached a sample workbook with fake data. The comparison is the biggest hurdle I am facing now, I can live with the results not exporting to a new sheet if need be.
Any help is greatly appreciated!