Deleting Rows from 3 sheets back on matching columns

**motown** · 05-07-2010, 11:18 AM

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** · 05-07-2010, 12:28 PM

Originally Posted by motown

... 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

**motown** · 05-07-2010, 01:30 PM

Originally Posted by davegugg

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** · 05-07-2010, 01:15 PM

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.

**JBeaucaire** · 05-07-2010, 02:40 PM

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.

Option Explicit

Sub Delete3SheetMatches()
'Jerry Beaucaire    5/6/2010
'Delete rows that match equal values on all 3 sheets
'https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/system/app/pages/sitemap/hierarchy
Dim ws1  As Worksheet:   Set ws1 = Sheets("MAS90")
Dim ws2  As Worksheet:   Set ws2 = Sheets("MT")
Dim ws3  As Worksheet:   Set ws3 = Sheets("TEDD")
Dim LR1  As Long:        LR1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
Dim LR2  As Long:        LR2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
Dim LR3  As Long:        LR3 = ws3.Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
On Error Resume Next

'Put in keys for matching easily
ws1.Range("AA2:AA" & LR1).FormulaR1C1 = "=RC1 & "" "" & RC3"
ws2.Range("AA2:AA" & LR2).FormulaR1C1 = "=RC2 & "" "" & RC3 & "" "" & RC7"
ws3.Range("AA2:AA" & LR3).FormulaR1C1 = "=RC2 & "" "" & RC3 & "" "" & RC5"
With ws1.Range("AB2:AB" & LR1)
    .FormulaR1C1 = _
        "=AND(COUNTIF(C27,RC27)=COUNTIF(MT!C27,RC27),COUNTIF(C27,RC27)=COUNTIF(TEDD!C27,RC27))"
    .Value = .Value
End With
ws1.Range("AB1") = "key"

With ws3
    .Range("AB1") = "key"
    .Range("AB2:AB" & LR3).FormulaR1C1 = _
        "=INDEX(MAS90!C28, MATCH(RC27, MAS90!C27, 0)) = TRUE"
    .Range("AB:AB").AutoFilter Field:=1, Criteria1:="TRUE"
    LR3 = .Range("A" & .Rows.Count).End(xlUp).Row
    If LR3 > 1 Then .Range("AB2:AB" & LR3).EntireRow.Delete xlShiftUp
    .AutoFilterMode = False
    .Range("AA:AB").ClearContents
End With

With ws2
    .Range("AB1") = "key"
    .Range("AB2:AB" & LR2).FormulaR1C1 = _
        "=INDEX(MAS90!C28, MATCH(RC27, MAS90!C27, 0)) = TRUE"
    .Range("AB:AB").AutoFilter Field:=1, Criteria1:="TRUE"
    LR2 = .Range("A" & .Rows.Count).End(xlUp).Row
    If LR2 > 1 Then .Range("AB2:AB" & LR2).EntireRow.Delete xlShiftUp
    .AutoFilterMode = False
    .Range("AA:AB").ClearContents
End With

With ws1
    .Range("AB:AB").AutoFilter Field:=1, Criteria1:="TRUE"
    LR1 = .Range("A" & .Rows.Count).End(xlUp).Row
    If LR1 > 1 Then .Range("AB2:AB" & LR1).EntireRow.Delete xlShiftUp
    .AutoFilterMode = False
    .Range("AA:AB").ClearContents
End With
    
Application.ScreenUpdating = True
End Sub