VBA to Compare Two Worksheets Based on Multiple Columns and Output Results to User Built Template

[goob90]

Hello everyone!



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)

Sheet One- Payroll Report

Sample Reconcile (1).xlsx
	A	B	C	D	E	F	G
1	First Name	Last Name	Employee ID	Product	Employee Portion	Employer Portion	Total Premium
2	Luke	Skywalker	1976	Health	$ 25.00	$ 475.00	$ 500.00
3	Luke	Skywalker	1976	Dental	$ 4.50	$ 25.50	$ 30.00
4	Egon	Spengler	1984	Dental	$ 4.50	$ 25.50	$ 30.00
5	Lloyd	Christmas	1994	Health	$50	$650	$700
6	Lloyd	Christmas	1994	Dental	$6	$34	$40
7	Clark	Griswald	1983	Health	$80	$ 820.00	$900
Sample Payroll Report

Cell Formulas
Range	Formula
G2:G7	G2	=SUM(E2,F2)

Sheet Two- Carrier Invoice

Sample Reconcile (1).xlsx
	A	B	C	D	E
1	First Name	Last Name	Employee ID	Product	Total Premium
2	Luke	Skywalker	1976	Health	$ 600.00
3	Luke	Skywalker	1976	Dental	$ 30.00
4	Egon	Spengler	1984	Dental	$ 30.00
5	Lloyd	Christmas	1994	Health	$ 700.00
6	Lloyd	Christmas	1994	Dental	$ 40.00
7	Peter	McCallister	1990	Health	$ 800.00
8	Peter	McCallister	1990	Dental	$ 100.00
Sample Carrier Invoice

New Workbook with Results

Sample Reconcile (1).xlsx
	A	B	C	D	E	F	G	H
1	First Name	Last Name	SSN	Product	Payroll Deduction	Inoivce Amount	Difference	Notes
2	Luke	Skywalker	1976	Health	$ 1,976.00	$ 600.00	$ (1,376.00)
3	Clark	Griswald	1983	Health	$ 900.00		$ (900.00)	Missing from Invoice
4	Peter	McCallister	1990	Health		$ 800.00	$ 800.00	Missing Payroll Deduction
5	Peter	McCallister	1990	Dental		$ 100.00	$ 100.00	Missing Payroll Deduction
Reconcile

Cell Formulas
Range	Formula
E2	E2	=SUM(C2,D2)
G2:G5	G2	=F2-E2

 

[goob90]

You're welcome, I was happy to help. Thanks for the feedback!

Please mark the post that answered your question as the solution to help future readers. Little check mark icon on the right side of the post.

This code has been so helpful for me at work, thank you again!

I have several values where the final difference is -0.01. Is there a way to exclude those from being flagged as "Invoice/Deduction Discrepancy"?

Basically, our deduction is a penny off from our invoice. It's not a huge deal, but it would be nice to leave those employees off.

I looked at the code and tried to figure it out myself with no luck. I'm guessing it would be somewhere in this area? If arrA(i, 7) <> 0 Then arrA(1, 8) = "Invoice/Deduction Discrepancy"

Test Reconcile VBA 2.xlsm
	A	B	C	D	E	F	G	H
2	Luke	Skywalker	1976	Health	599.99	600	-0.01	Invoice/Deduction Discrepancy
Reconcile

 

[igold]

Without going through the code again, if you replace the line you quoted

I'm guessing it would be somewhere in this area? If arrA(i, 7) <> 0 Then arrA(1, 8) = "Invoice/Deduction Discrepancy"

with this line, does it solve your issue

If arrA(i, 7) > 0.01 Or arrA(i, 7) < -0.01 Then arrA(1, 8) = "Invoice/Deduction Discrepancy"

 

[goob90]

Without going through the code again, if you replace the line you quoted

with this line, does it solve your issue

If arrA(i, 7) > 0.01 Or arrA(i, 7) < -0.01 Then arrA(1, 8) = "Invoice/Deduction Discrepancy"

I want to say thank you again, your code has been a huge help to me! I am having a slight issue and I have tried to figure it out on my own, but I can't seem to fix it.

When the code is run with "Sample Payroll Report" and "Sample Carrier Invoice" worksheets having over 1000 rows, the resulting "Reconcile" sheet shows that some individuals are marked as "Missing From Invoice" and "Missing Payroll Deduction", even though they appear on both sheets. The problem seems to affect anyone on a row after 999. Any idea how I could fix it?

 

[goob90]

I want to say thank you again, your code has been a huge help to me! I am having a slight issue and I have tried to figure it out on my own, but I can't seem to fix it.

When the code is run with "Sample Payroll Report" and "Sample Carrier Invoice" worksheets having over 1000 rows, the resulting "Reconcile" sheet shows that some individuals are marked as "Missing From Invoice" and "Missing Payroll Deduction", even though they appear on both sheets. The problem seems to affect anyone on a row after 999. Any idea how I could fix it?

Scratch that. The issue isn't with being over 1000 rows. I ran tests using dummy data with over 1000 rows with no issues. It just so happened that people showing Missing From Invoice" and "Missing Payroll Deduction" were listed after row 1000. I am not sure what the issue is, though I suspect that it would have to be in the formatting somewhere...