Comparing two excel sheets with different layouts

[secreit]

Hi everybody,

I have a question regarding a task which I do at work and which involves excel.

Since the real sheets contain sensitive information I've made two very simple example sheets which i attached below, blue is sheet1 & yellow is sheet2.
I hope this is enough to understand what I need.

Basically what I want to do is compare the arrival and price of each product in both sheets and show if there are any differences. Doing it manually takes me about 1 hour since the sheets are quite large and sometimes more than two sheets need to be checked.

Is there some program, macro, vba which is able to automatically complete this task?

As you can see on sheet2 each product is listed multiple times, so it would have to check each one.

Thanks for your help.

 

[MickG]

Try this:-
"Mismatches" shown in column "H".

[COLOR="Navy"]Sub[/COLOR] MG28Mar20
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, Rng [COLOR="Navy"]As[/COLOR] Range, Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] shts [COLOR="Navy"]As[/COLOR] Variant, Q [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray() [COLOR="Navy"]As[/COLOR] Variant, Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nCol [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ColA [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ColB [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] Variant

[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
 Dic.CompareMode = 1
  shts = Array("Sheet1", "Sheet2")
  
  [COLOR="Navy"]For[/COLOR] Ac = 0 To 1
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Ac
            [COLOR="Navy"]Case[/COLOR] 0: ColA = 4: ColB = 6
            [COLOR="Navy"]Case[/COLOR] 1: ColA = 3: ColB = 2
        [COLOR="Navy"]End[/COLOR] Select
        [COLOR="Navy"]With[/COLOR] Sheets(shts(Ac))
            [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
        [COLOR="Navy"]End[/COLOR] With
 
         [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
                [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
                [COLOR="Navy"]End[/COLOR] If
        
                [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, ColA).Value) [COLOR="Navy"]Then[/COLOR]
                    ReDim Preserve Ray(1 To 2, 1 To 1)
                    [COLOR="Navy"]Set[/COLOR] Ray(1, 1) = Dn.Offset(, ColB)
                    Dic(Dn.Value).Add (Dn.Offset(, ColA).Value), Ray
                [COLOR="Navy"]Else[/COLOR]
                    Q = Dic(Dn.Value).Item(Dn.Offset(, ColA).Value)
                        [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Q, 2)
                            [COLOR="Navy"]If[/COLOR] Not Dn.Offset(, ColB) = Q(1, n) [COLOR="Navy"]Then[/COLOR] Q(2, 1) = True
                        [COLOR="Navy"]Next[/COLOR] n
                        ReDim Preserve Q(1 To 2, 1 To UBound(Q, 2) + 1)
                        [COLOR="Navy"]Set[/COLOR] Q(1, UBound(Q, 2)) = Dn.Offset(, ColB)
                    Dic(Dn.Value).Item(Dn.Offset(, ColA).Value) = Q
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] Ac
  
  
    
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
        [COLOR="Navy"]If[/COLOR] Dic(k).Count > 1 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
                [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Dic(k).Item(p), 2)
                    Col = Dic(k).Item(p)(1, n).Column
                    nCol = IIf(Col = 7, 1, 5)
                    Dic(k).Item(p)(1, n).Offset(, nCol).Value = "MisMatch!!"
                [COLOR="Navy"]Next[/COLOR] n
            [COLOR="Navy"]Next[/COLOR] p
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
           [COLOR="Navy"]If[/COLOR] Dic(k).Item(p)(2, 1) = True [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Dic(k).Item(p), 2)
                    Col = Dic(k).Item(p)(1, n).Column
                    nCol = IIf(Col = 7, 1, 5)
                    Dic(k).Item(p)(1, n).Offset(, nCol).Value = "MisMatch !!"
               [COLOR="Navy"]Next[/COLOR] n
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] p
    [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick