Match Debits and Credits

Good afternoon!

I have a large amount of data (100K+ rows) where I need to match debits (position amounts) with offsetting credits (negative amounts) based on the the invoice. I have been using this function =ROUND(SUMIFS([AMT],[XREF],[XREF]),2)=0; however, it has not found all offsetting entries and takes forever! I am attaching a file with data, as an example, and would appreciate any help/thoughts to achieve this more quickly or to get a macro to accomplish this task.

Thank you!!!

The formula you gave above will produce TRUE if the net total of entries for that XREF is 0 (FALSE otherwise) and seems like a reasonable formula. What do you mean it has not found all entries? You have to make sure that all matching XREFs are stored identically--no spaces at the beginning or end.

You don't need SUMIFS because there is only one criterion. I tried this and it worked fine:

Formula:

Please Login or Register  to view this content.

See attached example.

I know this is just a small sample, but none of your XREF data nets to 0, so it's hard to give it a thorough test. I added couple of numbers to force a TRUE answer and that formula worked fine for me. If it doesn't work in your full data, you need to show enough data to demonstrate where it doesn't work.

If you put your file in a ZIP file and it's under 9MB, you can attach it. For some reason the limit for zip files is much larger than for Excel files.

Thank you for responding 6StringJazzer. I went back and looked at the data and discovered some of the "FALSE" are due to the condition being =0

Does that mean you solved your problem?

[SOLVED] My formula needed revision, a better formula, which you provided. Thanks again.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.