VBA for Comparing Values

[Geshwinn]

Hi guys,

I have another project ahead of me and need to write some macros to automate it.

I have two sheets with LOTS of rows and columns. The data in both sheets is about same items but taken from different sources. My job is to compare if there are any differences.

I am thinking of comparing data(the first sheet is the primary one) and if data doesn't match to color the text for the corresponding attribute in sheet1.

Example Sheet1
Items Date Country
apples 13-Mar Germany
pears 18-Apr Norway
melons 12-Jan China
strawberries 7-Feb Nigeria

Example Sheet2
Items Date Country
apples 13-Mar Germany
strawberries 21-May Nigeria
melons 12-Jan Japan
pears 18-Apr Norway

The thing is that the items names are not ordered in the same way as you can see.

What would be the most elegant way to do that?

P.S. I have more than 15 reports with 2000+ rows and I wouldn't like doing all that manually

 

[MickG]

Assumiing Actual data in both sheets starts in "A2" and there are 3 columns for each set of data, then try this:-

[COLOR="Navy"]Sub[/COLOR] MG21Mar49
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, Rng1 [COLOR="Navy"]As[/COLOR] Range, Rng2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Ac
[COLOR="Navy"]Dim[/COLOR] ShtRng [COLOR="Navy"]As[/COLOR] Range
With Sheets("Sheet1") '[COLOR="Green"][B]sht1[/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng1 = .Range(.Range("A2"), .Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
With Sheets("Sheet2") '[COLOR="Green"][B]sht2[/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng2 = .Range(.Range("A2"), .Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] Rn = 1 To 2
    [COLOR="Navy"]Set[/COLOR] ShtRng = IIf(Rn = 1, Rng1, Rng2)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] ShtRng
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.value) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.value, Array(Dn.Offset(, 1), Dn.Offset(, 2))
        [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] Rn = 2 [COLOR="Navy"]Then[/COLOR]
            Q = .Item(Dn.value)
                [COLOR="Navy"]For[/COLOR] Ac = 0 To 1
                    [COLOR="Navy"]If[/COLOR] Not Q(Ac) = Dn.Offset(, Ac + 1) [COLOR="Navy"]Then[/COLOR]
                    Q(Ac).Font.ColorIndex = 3
                    [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] Rn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick