Nested match offsetting amounts for accounting

  • 20,000 rows of data

    Need VBA to identify matching offsetting amounts (e.g. 500 and -500)
    But match has to be found nested within first Vendor Number, then PO#, then Project#, then Date added and only then by Amount
    Identifying matches by placing corresponding row number in a helper column would be ideal, so if 500 is in row 2 and matching -500 is in row 1132 both get “2” in helper column
    After macro is done, filtering to blanks in helper column would show only the amounts without matches that need to be researched

    Problems encountered so far in trying VBA or formula “solutions” have not seem to produce reliable or workable results:

    • No nested solutions
    • Had so stop macros running on ~18,000 rows after ~10 minutes, just to find out it only went through ~2,200 rows, so speed is relevant
    • Matching offsetting amounts were missed, reliability is relevant

    Maybe Excel is not meant for this task at all… Would be nice though

    Ideas, thoughts?

  • Hello,

    With 20'000 + rows .. have you tested a simple pivot table ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi, pivot table takes care of the speed, but... when it comes to Date Added for example - solution needs to look for offsetting amount from that date and forward