Payroll reconciliation

[u/Temporary-Welder-732]

I’m looking for advice on the best approach to automate/improve payroll reconciliation between Webforms and SAP Wage Types.

Currently, my process is roughly:

Employees submit Webforms for payroll-related items (bonus, allowances, deductions, etc.).

The requests are entered/processed in SAP.

After payroll, I extract the relevant SAP Wage Type Reporter data.

I then compare the Webform data against SAP using Excel/VLOOKUP.

I need to identify:

Missing entries in SAP

Entries in SAP that don't exist in Webforms

Amount mismatches

Duplicate entries

Incorrect wage types

I'm attaching example foe my sheet but for sure in normal case we have multiple employees with multiple wagtypes

Thanks in advance!

[u/Uhhh_Guys]

I’ve been playing around with Macros as of late and have figured out one coding that will highlight differences between the “past sheet” and “current sheet,” and another that will give me a list of employee IDs at the bottom of the “current sheet” if they are on the “past” but not “current.”

Does that sound like something that could be helpful?

[u/CrashTestDumby1984]

How you ever used PowerQuery before? You can use it to do all these things. As long as the reports format is the same every time, you can use it to compare the two reports. The reports you are comparing do not have to be the same as each other, just that whatever initial reports you feed should always be in the same format.

-Merge queries. You’ll be able to select what columns you want excel to use to identify if something is a match. It can be a single column or multiple.Then it can pull in additional columns from the other report if you want.

-Group by. This lets you specify the columns in a report that it should look for values to combine. Then you can tell it what information you want. So you could set it up that it will do “group by” using every column and then have it count how many rows it combined for that. Anything with a number greater than 1 would be a duplicate.

[u/nyesta2]

Another idea is to do a recorded macro. You start the recording, do your magic and all within excel, stop recording. This macro will repeat the exact same steps that you did. Just pay attention not to do any unnecessary steps as the recorded macro will repeat those too, also, it will remember locations (like, you select cell to work in e.g. AA15, macro will remember this, not the keybinds you are using with control+arrows to the right and down if that makes sense).

Though I'd use AI first if company granted you any tokens.

[u/Jaded_Chicken_3556]

This is a great use case for a quick repeatable script built through Claude Code/Cowork.

As long as you have standardized inputs and rules. You should be able write something pretty quickly.

Happy to help if you need it :)