VBA - Advanced Filter / Find Row Items to Remove Based on Multiple Criteria

I'm working with many rows of data (500,000+) and many columns. To simplify my question, I'm going to provide a simple example using made up numbers and only the columns I'm concerned with.

BillT Doc. Item Qty
F1 123 10 12.00
F2 123 20 5.00
S1 123 10 5.00
RE 321 20 2.00
F2 321 10 8.00
F2 321 20 1.00
RE 321 20 3.00
RE 999 80 8.00
F2 777 100 1.00
RE 777 100 1.00

I am trying to remove the docs that have two Bill types that cancel each other out, where the qtys match and highlight the rows where the qtys don't match.
The macro needs to have the positive and negative bill types programatically entered, where for example F1 and F2 are positive and S1 and RE are negative. Keep in mind the data may not necessarily be in order as it is above.

So for example with data above, the rows for doc 777 would be removed completely because the item numbers are the same, the qty is the same, and the bill types oppose each other. Doc 123 and Item 10 lines should be highlighted since their bill types are opposed bu their qtys don't match.

I hope this makes sense. I tired to achieve this using multiple loops and arrays, but ran out of memory when working with the entire set of data. I'm assuming their must be a better way to do this, I'm hoping some of the intelligent individuals here will be able to point in the right direction.

Thanks in advance for the assistance

F1 and F2 are positive. Everything else is negative.

Anybody? Any help or guidance would be much appreciated. If it helps at all here is the code I'm currently trying to make work, but I get an Out of Memory warning when working with all the data.

Please Login or Register  to view this content.

I'm hoping there is an easier way that doesn't require so many loops and arrays.

Thanks for your reply, Tsjallie.

I started running the code yesterday at 4:04 PM. It is currently 8:52 AM and the code has gotten through about 105,000 lines.

It appears to be working correctly however in the small test I did before running the entire thing. I will keep you posted on the end result.

I really appreciate the help.

No that wouldn't be a problem at all.

Subsequent running of this code will be with much smaller data sets, so the approach could still work. It's just this first time that the dataset is so large.