Deleting Rows from 3 sheets back on matching columns

[motown]

Hi Everyone,

I’ve bee working through this Marco for a while and for the life of me I just can’t figure out a working way to get this to go. I tried using an example that worked with 2 tabs in the past, but no dice.

What I am trying to do is delete lines that match on all 3 of the included tabs based on set criteria. All these reports will have 2000-10000 lines of reconciliation data to review in any given month, so I’m looking for a way to speed up the process.

Each sheet contains and “Amount”, but that’s really it, so this is what I’m thinking:

SHEET “MAS90” – Column A can be concatenated with Column C to make a string we can use to match with the other sheets
SHEET “MT” – Column B, Column C, and Column G can be concatenated to make the same string
SHEET “TEDD” – Column B, Column C, and Column E can be concatenated to make the same string.

Now, for those lines that match on all three sheets, the lines can be deleted. My only problem is if we charge three $500 fees for one person and two of the sheets capture all 3, and 1 report only captures 2 of them, is there a way to leave these on and highlight them for manual review?

Hope this makes sense and thanks in advance!

[davegugg]

... My only problem is if we charge three $500 fees for one person and two of the sheets capture all 3, and 1 report only captures 2 of them, is there a way to leave these on and highlight them for manual review?

Could you explain this part in better detail, I can't seem to wrap my mind around it.

Thanks

[JBeaucaire]

In terms of a macro, duplicate charges on the same sheet shouldn't be a problem. Since we are only deleting rows that match on all 3 sheets, we use sheet1 only as a basis.

1) Create your concatenated strings as you've already outlined above
2) Check the first string against the other sheets. If it matches on all 3 sheets, immediately delete all 3 and proceed to the next.

This way, if Sheets1 and 2 have 3 $500 charges but sheet3 only has two, the first two will be deleted on sheets 1 and 2 but the 3rd charge will remain.

And so on.

[motown]

Could you explain this part in better detail, I can't seem to wrap my mind around it.

Thanks

Some of our charges can be monthly and if a contract tells us to bill in quarterly terms, we could have "Joe Expat with three $500 fees that should be on every sheet. If one of those sheets is missing one of those lines I'd like to have none of them be deleted so I can go behind the scenes in the corresponding reporting system to see why the missing $500 didn't pull for a third time.

The reason for this is due to the software sometimes seeing it's own data as duplicated data and thus doesn't include it in the report. It's a glitch we current fix manually, so having this aspect would be a blessing.

does that help?

[JBeaucaire]

The the attached sheet with this macro. I used worksheet formulas and the AutoFilter to delete all the rows at once, should be peppy enough.

Please Login or Register  to view this content.