Compare two differents tables in separate worksheet & display data in a new worksheet

[Aimedija]

My question is ; is there a possibility to compare two excel worksheets with a different layout as below? I'm willing to compare an historical worksheets versus a new worksheets and display in a third worksheet what was on the new worksheet that does not exist in the historical e.g :

I hope you will understand my question and be able to help me on this topic. I already have a code which compare two worksheet and show the difference but it's not enough for my problem.
<code>Option Explicit

Sub CompareIt()
Dim ar As Variant
Dim arr As Variant
Dim Var As Variant
Dim v()
Dim i As Long
Dim n As Long
Dim j As Long
Dim str As String

ar = Sheet1.Cells(10, 1).CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
ReDim v(1 To UBound(ar, 2))
For i = 2 To UBound(ar, 1)
For n = 1 To UBound(ar, 2)
str = str & Chr(2) & ar(i, n)
v = ar(i, n)
Next
.Item(str) = v: str = ""
Next
ar = Sheet2.Cells(10, 1).CurrentRegion.Resize(, UBound(v)).Value
For i = 2 To UBound(ar, 1)
For n = 1 To UBound(ar, 2)
str = str & Chr(2) & ar(i, n)
v = ar(i, n)
Next
If .exists(str) Then
.Item(str) = Empty
Else
.Item(str) = v
End If
str = ""
Next
For Each arr In .keys
If IsEmpty(.Item(arr)) Then .Remove arr
Next
Var = .items: j = .Count
End With
With Sheet3.Range("a1").Resize(, UBound(ar, 2))
.CurrentRegion.ClearContents
.Value = ar
If j > 0 Then
.Offset(1).Resize(j).Value = Application.Transpose(Application.Transpose(Var))
End If
End With
End Sub</code>Thanks in advance

 

[Fluff]

Cross posted https://stackoverflow.com/questions...sheet-and-display-new-data-in-a-new-worksheet

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Fluff]

This will compare the 2 sheets & highlight the New sheet if the data is not in the original

Sub CompareShts()
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   Dim Cl As Range
   Dim ValU As String
   
   Set Ws1 = Sheets("[COLOR=#ff0000]Pcode[/COLOR]")
   Set Ws2 = Sheets("[COLOR=#ff0000]New[/COLOR]")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         ValU = Cl.Value & "|" & Cl.Offset(, 1).Value & "|" & Cl.Offset(, 2).Value & "|" & Cl.Offset(, 3).Value & "|" & Cl.Offset(, 4).Value
         If Not .exists(ValU) Then .Add ValU, Nothing
      Next Cl
      For Each Cl In Ws2.Range("C2", Ws2.Range("C" & Rows.Count).End(xlUp))
         ValU = Cl.Value & "|" & Cl.Offset(, 6).Value & "|" & Cl.Offset(, 7).Value & "|" & Cl.Offset(, 8).Value & "|" & Cl.Offset(, 9).Value
         If Not .exists(ValU) Then Cl.EntireRow.Interior.Color = vbYellow
      Next Cl
   End With
End Sub

Change sheet names in red to suit

 

[Aimedija]

Hi,
Thank you but it does highlight all data (different or the same) in the compare sheet. Do you have another code that can be used here?

Thanks

This will compare the 2 sheets & highlight the New sheet if the data is not in the original

Sub CompareShts()
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   Dim Cl As Range
   Dim ValU As String
   
   Set Ws1 = Sheets("[COLOR=#ff0000]Pcode[/COLOR]")
   Set Ws2 = Sheets("[COLOR=#ff0000]New[/COLOR]")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         ValU = Cl.Value & "|" & Cl.Offset(, 1).Value & "|" & Cl.Offset(, 2).Value & "|" & Cl.Offset(, 3).Value & "|" & Cl.Offset(, 4).Value
         If Not .exists(ValU) Then .Add ValU, Nothing
      Next Cl
      For Each Cl In Ws2.Range("C2", Ws2.Range("C" & Rows.Count).End(xlUp))
         ValU = Cl.Value & "|" & Cl.Offset(, 6).Value & "|" & Cl.Offset(, 7).Value & "|" & Cl.Offset(, 8).Value & "|" & Cl.Offset(, 9).Value
         If Not .exists(ValU) Then Cl.EntireRow.Interior.Color = vbYellow
      Next Cl
   End With
End Sub

Change sheet names in red to suit

 

[Fluff]

Is your data exactly as shown in the images?

 

[Aimedija]

Yes indeed exactly the same so : Sheet 1 = Historical data / Sheet 2 = New data / Sheet 3 = what I'm looking for.

 

[Fluff]

In that case check for leading/trailing spaces.
When I ran the code on a mockup of your data it worked.

 

[Aimedija]

Alright , I will try again and let you know. Thanks

 

[Aimedija]

Hi,

It does work perfectly, thanks a lot

 

[Fluff]

Glad to help & thanks for the feedback