Combine every two rows in a spreadsheet into one based on criteria

Use Power Query to group each pair by Transaction ID, select the asset/liability row and expense/income row, and return one record containing fields from both rows.

Create the query in Excel for Mac

Power Query Editor is generally available to Microsoft 365 subscribers using Excel for Mac version 16.69 or later.^1^

1. Select the exported data and format it as an Excel table.
2. On Table Design, change the table name to Transactions.
3. Select Data > Get Data (Power Query) > Launch Power Query Editor.
4. Create a query from the Transactions table.
5. Open Advanced Editor and replace the query with the code below.
6. Select Done, then Home > Close & Load.

Power Query’s Table.Group operation creates one result for each unique grouping key—in this case, each Transaction ID.^2^

let
    Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
    Grouped = Table.Group(Source, {"Transaction ID#"}, {
        {"Result", (t) =>
            let
                AccountRows = Table.SelectRows(t, each
                    List.Contains({"asset", "liability"},
                        Text.Lower(Text.From([Account Group])))),
                CategoryRows = Table.SelectRows(t, each
                    List.Contains({"expense", "income"},
                        Text.Lower(Text.From([Account Group])))),
                Common = t{0},
                Account = try AccountRows{0} otherwise null,
                Category = try CategoryRows{0} otherwise null
            in [
                TransactionDate = Common[Transaction Date],
                AccountGroup = try Account[Account Group] otherwise null,
                CategoryGroup = try Category[Account Group] otherwise null,
                AccountType = try Account[Account Type] otherwise null,
                CategoryType = try Category[Account Type] otherwise null,
                AccountName = try Account[Account Name] otherwise null,
                CategoryName = try Category[Account Name] otherwise null,
                TransactionDescription = Common[Transaction Description],
                Amount = try Category[Amount] otherwise null,
                Vendor = Common[Vendor],
                Customer = Common[Customer]
            ], type record}
    }),
    Expanded = Table.ExpandRecordColumn(Grouped, "Result", {
        "TransactionDate", "AccountGroup", "CategoryGroup",
        "AccountType", "CategoryType", "AccountName", "CategoryName",
        "TransactionDescription", "Amount", "Vendor", "Customer"
    }, {
        "Transaction Date", "Account Group", "Category Group",
        "Account Type", "Category Type", "Account Name", "Category Name",
        "Transaction Description", "Amount", "Vendor", "Customer"
    })
in
    Expanded

The result has one row per Transaction ID, with:

• Account fields taken from the asset or liability row.
• Category fields taken from the expense or income row.
• Amount taken from the expense/income row.
• Date, description, vendor, and customer taken from the first row in the pair.

The query does not depend on the two source rows being adjacent. Power Query groups all rows sharing the same key, and its All rows behavior can retain the complete set of records inside each group.^2^

Adjustments and validation

• The column names in the code must exactly match the headers in the export. For example, change "Transaction ID#" if the actual header is "Transaction ID".
• Comparisons are case-insensitive, but the group values must otherwise be asset, liability, expense, or income.
• A missing account/category side produces blank fields rather than stopping the query.
• If one Transaction ID can contain more than one account row or category row, the query currently uses the first matching row. An aggregation rule—such as summing multiple category amounts—would need to be defined. Power Query supports aggregate operations such as Sum, Min, Max, and Count during grouping.^2^
• After replacing the accounting export, select Data > Refresh All to regenerate the import table.^1^

If Launch Power Query Editor is unavailable, check the Excel version under Excel > About Microsoft Excel. Power Query support differs across Mac versions and editions.^3^

References

1. Import and shape data in Excel for Mac (Power Query)
2. Grouping or summarizing rows
3. About Power Query in Excel