How to Match Groups of Transactions Between Two Columns in Excel
Question details
The user needs a way to match groups of split transactions in one column to a total payment amount in another column for bank reconciliation.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Reconciling bank transactions where multiple smaller payments split across different dates need to be matched to a single consolidated total (a subset-sum problem).
- Observed behavior
- Finding the correct match is difficult because multiple mathematical combinations of the split transactions can produce the same total, requiring advanced rules or tools to isolate the correct group.
Ensure your transaction data is formatted as numbers without extra spaces, and sort your columns by date, vendor, or transaction type to narrow down potential matching groups before running calculations.
Use the Excel Solver Add-in for Subset-Sum Matching
The Solver add-in can calculate which combinations of values in a list sum up to a specific target transaction value.
Because multiple combinations might reach the same total, it is highly recommended to filter your data by date range or transaction type first to improve the accuracy of the Solver's results.
Go to File > Options > Add-ins. In the Manage drop-down at the bottom, select Excel Add-ins and click Go. Check the box for 'Solver Add-in' and click OK.
Create a new column next to your list of split transactions. Enter the number 1 in each cell of this helper column for the rows you want to test.
In an empty cell, enter a SUMPRODUCT formula that multiplies your transaction amounts range by your helper column range (e.g., =SUMPRODUCT(A2:A15, B2:B15)).
Go to the Data tab and click Solver. Set the 'Set Objective' box to your SUMPRODUCT cell. Choose 'Value of' and enter the target consolidated transaction amount.
Set 'By Changing Variable Cells' to your helper column range. Click Add to create a constraint, select your helper column range, choose 'bin' (binary) from the drop-down, and click OK. Finally, click Solve to let Excel find the matching transactions.

Use Power Query to Group and Reconcile Transactions
If you have a large dataset with common identifiers like invoice numbers or specific payment dates, Power Query can group and sum these transactions quickly.
Reconcile Transactions Easily with WPS Spreadsheet
WPS Office provides powerful data analysis tools, including comprehensive SUMPRODUCT functions and What-If Analysis, to help you match transaction groups effectively. It offers a familiar interface and seamless compatibility with Microsoft Excel workbooks.
- 1. Open your transaction spreadsheet: Launch WPS Office and open your .xlsx file containing the bank transactions.
- 2. Filter your data: Use the Filter tool under the Data tab to narrow down transactions by date range or specific payment type, reducing the number of variables.
- 3. Apply calculation functions: Use the SUMPRODUCT function to test combinations of split payments against your target total, just as you would in standard spreadsheet software.
- 4. Utilize Goal Seek: Navigate to the Data tab and use the What-If Analysis > Goal Seek feature for basic target value calculations to find missing variables.

Frequently Asked Questions
Why does Excel find multiple matches for my transaction total?
In a subset-sum problem with many smaller amounts, multiple different combinations can mathematically equal the exact same total. You must add secondary rules, such as matching date ranges or specific payment types, to identify the factually correct match.
Can I match transactions without using Solver or VBA?
Yes, if the dataset is very small. You can manually sort the data by date or vendor and use simple SUM formulas to test a few combinations. However, as the number of transactions increases, manual calculation becomes too time-consuming.
How many transactions can Excel Solver handle for subset sums?
The standard Excel Solver is limited to 200 variable cells. If you need to match against a list of more than 200 split transactions in a single calculation, you will need to rely on VBA macros, Power Query grouping, or third-party reconciliation software.




