Function Problems

How to Match Groups of Transactions Between Two Columns in Excel

Tauseeq MagsiTauseeq Magsi Oct 7, 2026 871 views

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.

How to Match Groups of Transactions Between Two Columns in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enable the Solver Add-in

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.

2
Set up a helper column

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.

3
Create a SUMPRODUCT formula

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)).

4
Configure Solver parameters

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.

5
Add binary constraints and solve

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 the Excel Solver Add-in for Subset-Sum Matching
Identify the Match: Once Solver finishes, the cells in your helper column that changed to '1' indicate the transactions that make up the target total.
Efficient Data Reconciliation Tool

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. 1. Open your transaction spreadsheet: Launch WPS Office and open your .xlsx file containing the bank transactions.
  2. 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. 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. 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.
Fully compatible with Microsoft Excel (.xlsx) formulas and formattingAdvanced sorting and filtering tools to organize transaction dates and typesLightweight application with high performance for managing large datasetsFree to download and use for everyday accounting and reconciliation tasks
microsoft office alternative - wps office

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.