Macro to Compare Data on two sheets

[Howardc1001]

I have code below that I tried to adapt per items 1 & 2 below but cannot get the code to give me the correct result


1) Where the Reference in Col A and the value in Col D on Sheet ?Statement Current Month? does not match the Reference in Col F and value in Col J on sheet ?Purchase Ledger?, then the unmatched items to be extracted to sheet ?Statement Recon Items?
2) Where the Reference in Col F and the value in Col J on Sheet ?Purchase Ledger? does not match the Reference in Col A and value in Col D on sheet ?Statement Current Month? , then the unmatched items to be extracted to sheet ?PL Recon Items?


It would be apprciated if someone could either amend my code or provide new code to accomodate my request

Please Login or Register  to view this content.

[bebo021999]

One way:

Option Explicit 
Sub ReconItemsList() 
Dim lr&, i&, j&, rng, arST(), arPL() 
 
' delete old sheet 
If Evaluate("=ISREF('Statement Recon Items'!A1)") Then Sheets("Statement Recon Items").Delete 
If Evaluate("=ISREF('PL Recon Items'!A1)") Then Sheets("PL Recon Items").Delete 
 
'dupplicate sheet 
Sheets("Statement Current Month").Copy after:=Sheets(Sheets.Count) 
ActiveSheet.Name = "Statement Recon Items" 
Sheets("Purchase Ledger").Copy after:=Sheets(Sheets.Count) 
ActiveSheet.Name = "PL Recon Items" 
 
'copy data into array 
With Sheets("Statement Recon Items") 
    lr = .Cells(Rows.Count, "A").End(xlUp).Row 
    arST = .Range("A2:G" & lr).Value 
End With 
With Sheets("PL Recon Items") 
    lr = .Cells(Rows.Count, "A").End(xlUp).Row 
    arPL = .Range("A2:L" & lr).Value 
End With 
 
'check each row in both array, if both match then mark last column (helper column) of both arrays with "error sign" 
For i = 1 To UBound(arST) 
    For j = 1 To UBound(arPL) 
        If arST(i, 1) = arPL(j, 6) And arST(i, 4) = arPL(j, 10) Then 
            arST(i, 7) = Evaluate("=1/0"): arPL(j, 12) = Evaluate("=1/0") 
        End If 
    Next 
Next 
 
' paste array back to sheet, then delete rows those with "error sign" 
With Sheets("Statement Recon Items") 
    .Range("A2:G10000").ClearContents 
    .Range("A2").Resize(UBound(arST), 7).Value = arST 
    .Range("G2:G10000").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete 
End With 
With Sheets("PL Recon Items") 
    .Range("A2:L10000").ClearContents 
    .Range("A2").Resize(UBound(arPL), 12).Value = arPL 
    .Range("L2:L10000").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete 
End With 
End Sub  

[Howardc1001]

Many Thanks for the help. Your code works 100%

I have tried to understand the logic behind yoir code and I understand everything except the code below

Please explain how this works

Please Login or Register  to view this content.

[bebo021999]

Many Thanks for the help. Your code works 100%
I have tried to understand the logic behind yoir code and I understand everything except the code below
Please explain how this works

Nice to know that you are willing to learn!
The best way is, remove the 2 lines those delete helper column

... 
.Range("G2:G10000").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete 
... 
 .Range("L2:L10000").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete  

Then easy to see what is written in those columns!

[jindon]

Advanced filter

Please Login or Register  to view this content.

[Howardc1001]

Thanks bebo021999

[Howardc1001]

Many Thanks Jindon. Your code works perfectly

[jindon]

You are welcome and thanks for the rep.