How to Match 2 rows in excel spreadsheet by 2 columns?

GhostStage Source

I have an issue where I'm currently using several queries to achieve the outcome I want, but when run on larger spreadsheets it slows down substantially. Some info:

  • The Excel workbook in question has the .xlsx extension;
  • The two sheets in the Excel file are Matched and `Unmatched';
  • Connection type can use either ODBC or OleDB;
  • All data that needs to be matched is located on one sheet in Excel;
  • Matched data needs to be in pairs so must equal 2;
  • The 2 columns to be matched are Receipt and Amount where the Receipt on the two rows must match exactly and the Amount value for both rows must balance or equal 0. One Amount value will have a negative sign (-) at the beginning of it so the amounts can be added together to balance out, but they are on separate rows;
  • The number of rows can range from a couple hundred to several thousand (~60,000); and
  • If the two rows match/balance by Receipt and Amount, then they need to be moved into the Matched sheet in the same Excel file.
  • Scripts are not out of the question, VBScript and Powershell are welcome.

Example:

Example Excel Image

As you can see in the Excel example, I need to match 2 rows where the Receipt value matches exactly, and the Amount values balance. If both conditions are met, then I need to insert said rows into the Matched sheet. So in this example, both rows with 101010 in the Receipt column (rows 2 and 3) have amounts that balance in the Amount column, so they would be moved to Matched as both conditions are met. The remaining two values in Receipt match, but their Amount values do not balance, so the conditions would not be met for them.

The limitations are the statements available to the ODBC and OleDB connections. I am currently using several queries to match and return the row count of the above conditions and if it equals two, then I know to insert all rows of that Receipt number into the Matched sheet; however, like I said, I need it to be more efficient and to perform faster.

sqlexcelodbcoledb

Answers

answered 3 months ago Jan #1

Here's a VBA suggestion - it expects the original data to be in a tab called 'Original' and will sort on Receipt, then do a couple of formulas to determine receipt#'s that tally to zero then creates a new tab called 'Match' with matched receipt rows and another called 'Unmatch' with unmatched receipts.

    Sub TallySplit()
        vLR = Cells(Rows.Count, 1).End(xlUp).Row
        ActiveWorkbook.Worksheets("Original").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Original").Sort.SortFields.Add Key:=Range("A2:A" & vLR), _
            SortOn:=xlSortOnValues, Order:=xlAscending,         DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Original").Sort
            .SetRange Range("A1:D" & vLR)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("E2").Select
        ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-4]=RC[-4],R[-1]C[-3] + RC[-3],RC[-3])"
        Range("E2").Select
        Selection.AutoFill Destination:=Range("E2:E" & vLR)
        Range("E2:E" & vLR).Select
        Range("F2").Select
        ActiveCell.FormulaR1C1 = "=IF(RC[-5]=R[1]C[-5],R[1]C,RC[-1])"
        Range("F2").Select
        Selection.AutoFill Destination:=Range("F2:F" & vLR)
        Range("F2:F" & vLR).Select
        Range("A1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$F$" & vLR).AutoFilter Field:=6, Criteria1:="<>0", _
            Operator:=xlAnd
        Range("A1:D" & vLR).Select
        Selection.Copy
        Sheets.Add.Name = "Unmatch"
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Original").Select
        ActiveSheet.Range("$A$1:$F$" & vLR).AutoFilter Field:=6, Criteria1:="=0", _
            Operator:=xlAnd
        Range("A1:D" & vLR).Select
        Selection.Copy
        Sheets.Add.Name = "Match"
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End Sub

comments powered by Disqus