Excel Payment Reconciliation Automation
Budget / Salary$30–250
TypeFreelance project
LocationRemote
Posted1 hour ago
I work with two separate Excel exports every day.
• Sheet A lists every customer invoice that was paid by credit card, ACH, or an online bank payment.
• Sheet B shows the exact dollar amount that actually sent to the bank for each invoice.
What I need is a single, formula-driven workbook where I can simply paste those two raw tables and instantly see:
– which invoice numbers line up,
– whether the payment amounts and dates agree, and
– which payment method (ACH, credit card, or bank account draft) was used.
The workflow I imagine:
1. Paste Sheet A in Tab 1 and Sheet B in Tab 2.
2. Tab 3 automatically performs the reconciliation, flagging perfect matches, partial matches (amount or date off), and any missing items from either side.
3. A clear summary totals the matched and unmatched dollar values so I can hand it straight to accounting or spot issues at a glance.
Feel free to build this with the lookup or pivot techniques you prefer—VLOOKUP, INDEX-MATCH, Power Query, or even a lightweight macro—so long as it remains easy for a non-technical user to maintain and expand. Consistent naming, tidy formulas, and comments explaining any key logic will be appreciated.
When we’re done I should be able to paste fresh data, hit Refresh (or a single macro button), and walk away with an accurate reconciliation in seconds.
• Sheet A lists every customer invoice that was paid by credit card, ACH, or an online bank payment.
• Sheet B shows the exact dollar amount that actually sent to the bank for each invoice.
What I need is a single, formula-driven workbook where I can simply paste those two raw tables and instantly see:
– which invoice numbers line up,
– whether the payment amounts and dates agree, and
– which payment method (ACH, credit card, or bank account draft) was used.
The workflow I imagine:
1. Paste Sheet A in Tab 1 and Sheet B in Tab 2.
2. Tab 3 automatically performs the reconciliation, flagging perfect matches, partial matches (amount or date off), and any missing items from either side.
3. A clear summary totals the matched and unmatched dollar values so I can hand it straight to accounting or spot issues at a glance.
Feel free to build this with the lookup or pivot techniques you prefer—VLOOKUP, INDEX-MATCH, Power Query, or even a lightweight macro—so long as it remains easy for a non-technical user to maintain and expand. Consistent naming, tidy formulas, and comments explaining any key logic will be appreciated.
When we’re done I should be able to paste fresh data, hit Refresh (or a single macro button), and walk away with an accurate reconciliation in seconds.
Apply on Freelancer →
Project sourced from Freelancer.com. Applications happen directly on the original platform — we never collect your data.