Function Problems

How to Match Excel Payments by ID and Date in One Row

Steve K Sep 27, 2026 869 views

Question details

The user needs to match payment records by an ID and a specific date, and display all corresponding payment amounts across multiple columns in a single row.

How to Match Excel Payments by ID and Date in One Row

Product: Excel
Device & OS: not provided
Scenario: Organizing and consolidating payment transactions to easily identify repeated payments or missing entries based on unique ID and date combinations.
Observed behavior: The user wants to extract matched data into a single row format, but experienced issues getting the secondary TOROW and FILTER formula to output the desired results.

Before you start

Ensure you are using a modern version of your spreadsheet software (such as Microsoft 365, Excel 2021, or the latest WPS Office) that supports dynamic array functions like UNIQUE, FILTER, and TOROW.

Solution 1Recommended

Use Dynamic Array Formulas to Extract and Transpose Matches

Generate a deduplicated list of IDs and dates using UNIQUE, then use FILTER combined with TOROW to pull all matching payments into a single row.

This method uses dynamic array functions to automatically spill data into adjacent cells without the need for manual cell dragging or complex traditional array formulas.

Extract unique ID and date combinations

In your summary sheet (e.g., Sheet1), click on cell A2 and enter the formula `=UNIQUE(FILTER(Sheet2!A2:B100,Sheet2!A2:A100<>0))` to generate a deduplicated list of IDs (Column A) and dates (Column B).

Extract and transpose matched payments

Click on cell D2 and enter the formula `=TOROW(FILTER(Sheet2!$C$2:$C$100,(Sheet2!$A$2:$A$100=$A2)*(Sheet2!$B$2:$B$100=$B2)))`. This filters the payments in Column C based on the ID and Date, and TOROW turns the vertical result into a single horizontal row.

Adjust data ranges and apply

Make sure your sheet names (like 'Sheet2') and data ranges (like 'A2:B100') exactly match your actual workbook. Press Enter, then drag the fill handle in cell D2 down to apply this formula to the rest of your unique records.

Use Dynamic Array Formulas to Extract and Transpose Matches

Troubleshooting Formula Errors: If the second formula returns a #CALC! error or nothing happens, verify that there are no trailing spaces in your ID and Date columns. The values in Sheet1 and Sheet2 must match exactly.

WPS Spreadsheet Solutions

Manage Payment Records Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas like UNIQUE, FILTER, and TOROW, allowing you to seamlessly organize, match, and transpose complex payment data without compatibility issues.

1. Open your data file: Launch WPS Spreadsheet and open your payment records workbook.
2. Apply the UNIQUE formula: Select a blank cell and type `=UNIQUE(FILTER(...))` to generate your distinct list of IDs and payment dates.
3. Use TOROW and FILTER: Input `=TOROW(FILTER(...))` in the adjacent cell to instantly pull and display all matching payment amounts horizontally across the row.

Fully compatible with Microsoft Excel dynamic array formulasSeamlessly handles large financial transaction datasetsFree, lightweight, and easy-to-use interfaceSupports advanced data filtering and extraction natively

microsoft office alternative - wps office

Frequently Asked Questions

Why does my FILTER formula return a #CALC! error?

The #CALC! error occurs when the FILTER function finds no matching records for the given ID and date. You can avoid this by utilizing the built-in [if_empty] argument in the FILTER function, for example: `=FILTER(range, criteria, "No Match")`.

Can I use UNIQUE and TOROW in older versions of Excel?

No, UNIQUE, FILTER, and TOROW are dynamic array functions available only in Microsoft 365, Excel 2021 and newer, or modern versions of WPS Office. If you are using an older version, you would have to use complex INDEX and MATCH array formulas executed with Ctrl+Shift+Enter.

What if I only want to sum the matching payments instead of listing them?

If you do not need to see individual transactions and just want the total amount for a specific ID and date, use the SUMIFS function instead. Enter a formula like `=SUMIFS(Sheet2!C:C, Sheet2!A:A, A2, Sheet2!B:B, B2)` to calculate the total sum.

Excel & Data Problems