find duplicates in different sheets

[mrkasten]

I am using Excel 2010 and I need to find a way to find duplicated items that are in different worksheets. One sheet is a long list of none cashed checks and each month I get a new list and I need to find a way to see if any of the checks on the original list have been cashed. The checks that have been cashed will not be on the new sheet that I get every month.

 

[Comfy]

Double post.

 

[mrkasten]

At the moment the the columns change from sheet to sheet but I can rearrange them so the check number is in the same place on both. At the moment the check number is located in column K in the old list and column E in the new list

 

[Comfy]

Only tested in 2007.
Make a copy of your data before running this.

You will also need to change the worksheet names to Old and New or changed the code to your worksheet names

Sub ChequeRemove()
Dim wb As Workbook, oWS As Worksheet, nWS As Worksheet
Dim i As Long, LastRow As Long, LastCol As Long, nLastRow As Long
Set wb = ActiveWorkbook
Set oWS = wb.Sheets("Old")
Set nWS = wb.Sheets("New")

nLastRow = nWS.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
LastRow = oWS.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
LastCol = oWS.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Column

oWS.Range(Cells(1, LastCol + 1), Cells(LastRow, LastCol + 1)).FormulaR1C1 = "=VLOOKUP(RC[" & 11 - (LastCol + 1) & "]," & nWS.Name & "!R1C5:R" & nLastRow & "C5,1,FALSE)"

For i = LastRow To 1 Step -1
    If oWS.Cells(i, (LastCol + 1)).Text = "#N/A" Then Rows(i).EntireRow.Delete
Next i
End Sub

 

[mrkasten]

When i enter it after changing the names i get a box that has me open the file and pick a sheet to update from. Than no matter which sheet i click i get #NAME? in the new row. I could be entering it into the wrong spot though since this is my first time using VB.
I am using 2010 by the way.
Thanks

 

[mrkasten]

O and here is what my equation to find the cashed checks
=IF(ISNA(VLOOKUP(K2, 'June 2010'!D:D, 1, 0)),"Cashed","")

 

[Comfy]

When i enter it after changing the names i get a box that has me open the file and pick a sheet to update from. Than no matter which sheet i click i get #NAME? in the new row. I could be entering it into the wrong spot though since this is my first time using VB.
I am using 2010 by the way.
Thanks

Post the code how it appears once you made your changes

 

[mrkasten]

Sub ChequeRemove()
Dim wb As Workbook, oWS As Worksheet, nWS As Worksheet
Dim i As Long, LastRow As Long, LastCol As Long, nLastRow As Long
Set wb = ActiveWorkbook
Set oWS = wb.Sheets("Outstanding ST Loans")
Set nWS = wb.Sheets("June 2010")

nLastRow = nWS.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
LastRow = oWS.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
LastCol = oWS.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Column

oWS.Range(Cells(1, LastCol + 1), Cells(LastRow, LastCol + 1)).FormulaR1C1 = "=VLOOKUP(RC[" & 11 - (LastCol + 1) & "]," & nWS.Name & "!R1C5:R" & nLastRow & "C5,1,FALSE)"

For i = LastRow To 1 Step -1
If oWS.Cells(i, (LastCol + 1)).Text = "Cashed" Then Rows(i).EntireRow.Delete
Next i
End Sub

 

[Comfy]

Sub ChequeRemove()
Dim wb As Workbook, oWS As Worksheet, nWS As Worksheet
Dim i As Long, LastRow As Long, LastCol As Long, nLastRow As Long
Set wb = ActiveWorkbook
Set oWS = wb.Sheets("Outstanding ST Loans")
Set nWS = wb.Sheets("June 2010")

nLastRow = nWS.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
LastRow = oWS.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
LastCol = oWS.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Column

oWS.Range(Cells(1, LastCol + 1), Cells(LastRow, LastCol + 1)).FormulaR1C1 = "=VLOOKUP(RC[" & 11 - (LastCol + 1) & "],'" & nWS.Name & "'!R1C5:R" & nLastRow & "C5,1,FALSE)"

For i = LastRow To 1 Step -1
If oWS.Cells(i, (LastCol + 1)).Text = "#N/A" Then Rows(i).EntireRow.Delete
Next i
End Sub

Use that. Don't change #N/A to Cashed

 

[mrkasten]

It inserts a new row that is just 0's all the way down

Thanks again

 

[mrkasten]

I just realized a mistake i made earlier. The column in the new sheet that has the check number is column D. Sorry for the mix up.