Compare entry from 1 tab to same entry in 2nd tab

[rifpool]

Got many tabs 2010/2011, 2009/2010, 2008/2009, etc.

Columns are "Last Name" -- "First Name" -- "A number from 0-12"

What I want to do, is take the "last name" & "first name" from tab 2010/2011 and see if that 3rd column entry is 0 (zero).

If it is 0, then compare it to the same name in tab 2009/2010 & 2008/2009 and see if that 3rd column is 0 in those tabs.

Then come up with a "True" statement (if all tabs/entries are 0) that puts a number into another cell in tab 2010/2011.

Cripes, I could barely think up what I need so if you can solve this then I'll be really amazed. Thanks in advance for any answers.

 

[John Davis]

Got many tabs 2010/2011, 2009/2010, 2008/2009, etc.

Columns are "Last Name" -- "First Name" -- "A number from 0-12"

What I want to do, is take the "last name" & "first name" from tab 2010/2011 and see if that 3rd column entry is 0 (zero).

If it is 0, then compare it to the same name in tab 2009/2010 & 2008/2009 and see if that 3rd column is 0 in those tabs.

Then come up with a "True" statement (if all tabs/entries are 0) that puts a number into another cell in tab 2010/2011.

Cripes, I could barely think up what I need so if you can solve this then I'll be really amazed. Thanks in advance for any answers.

I think I understand, and this is very akward but try it on a copy.

Sub rifpool()
' This assumes that Column A = Last Name, Column B = First Name and Column C = Number
' Also Column D will indicate TRUE
Dim lr As Long
Dim lr2 As Long
Dim lr3 As Long

Application.ScreenUpdating = False

lr = Sheets("2010-2011").Cells(Rows.Count, 2).End(3).Row
lr2 = Sheets("2009-2010").Cells(Rows.Count, 2).End(3).Row
lr3 = Sheets("2008-2009").Cells(Rows.Count, 2).End(3).Row

Sheets("2010-2011").Columns("A:A").Insert Shift:=xlToRight
Sheets("2009-2010").Columns("A:A").Insert Shift:=xlToRight
Sheets("2008-2009").Columns("A:A").Insert Shift:=xlToRight

With Sheets("2010-2011")

    With Range("A2:A" & lr)

        .Formula = "=CONCATENATE(B2,C2)"

        .Value = .Value
        
    End With
    
End With

With Sheets("2009-2010")

    With Range("A2:A" & lr2)

        .Formula = "=CONCATENATE(B2,C2)"

        .Value = .Value
        
    End With
    
End With

With Sheets("2008-2009")

    With Range("A2:A" & lr3)

        .Formula = "=CONCATENATE(B2,C2)"

        .Value = .Value
        
    End With
    
End With

With Sheets("2010-2011")

Dim i As Long

    .Columns("E:F").Insert Shift:=xlToRight
    .Range("E2:E" & lr).Formula = "=VLOOKUP(A2,'2009-2010'!$A$2:$D$1000,4,FALSE)"
    .Range("E2:E" & lr).Value = .Range("E2:E" & lr).Value
    .Range("F2:F" & lr).Formula = "=VLOOKUP(A2,'2008-2009'!$A$2:$D$1000,4,FALSE)"
    .Range("F2:F" & lr).Value = .Range("F2:F" & lr).Value
    .Range("E2:F" & lr).Replace What:="#N/A", Replacement:="", LookAt:=xlWhole
    .Columns("G:G").Insert Shift:=xlToRight
    
For i = lr To 2 Step -1

    If Range("D" & i) = 0 And Range("E" & i) = 0 And Range("F" & i) = 0 Then Range("G" & i) = "TRUE"
    
Next i
    
    .Columns("E:F").Delete Shift:=xlToLeft
    .Columns("A:A").Delete Shift:=xlToLeft

    
End With

Sheets("2009-2010").Columns("A:A").Delete Shift:=xlToLeft
Sheets("2008-2009").Columns("A:A").Delete Shift:=xlToLeft


Application.ScreenUpdating = True

End Sub