VBA find matching ID then compare other values in row

[Kinjal Doshi]

To hiker95

I went through your earlier post - https://www.mrexcel.com/forum/excel...atching-id-then-compare-other-values-row.html

It was very helpful.. but it works only on 6th column. My requirement is that it works in all the columns that my excel sheet has... please check if you can help me out...

Thanks
Kinjal.

 

[MickG]

Try this:-
If you have some empty cells in the 39 columns, that could give you a problem.

[COLOR=navy]Sub[/COLOR] MG09Jul10
'[COLOR=green][B]Code3[/B][/COLOR]
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] t
[COLOR=navy]Dim[/COLOR] Rng2 [COLOR=navy]As[/COLOR] Range, Dic [COLOR=navy]As[/COLOR] Object, Q [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR=navy]With[/COLOR] Sheets("Sheet1")
    [COLOR=navy]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
      [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
            [COLOR=navy]If[/COLOR] Not IsEmpty(Dn.Value) [COLOR=navy]Then[/COLOR]
            [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]For[/COLOR] ac = 1 To 39
                    [COLOR=navy]If[/COLOR] Not IsEmpty(.Range("A1").Offset(, ac).Value) [COLOR=navy]The[/COLOR]
                    Dic(Dn.Value).Add .Range("A1").Offset(, ac).Value, _
                    Intersect(Dn.EntireRow, .Range("A1").Offset(, ac).EntireColumn)
                   [COLOR=navy]End[/COLOR] If
                [COLOR=navy]Next[/COLOR] ac
                
       [COLOR=navy]End[/COLOR] If
     [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] With

[COLOR=navy]With[/COLOR] Sheets("Sheet2")
    [COLOR=navy]Set[/COLOR] Rng2 = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng2
    [COLOR=navy]For[/COLOR] ac = 1 To 39
     [COLOR=navy]If[/COLOR] Dic.exists(Dn.Value) [COLOR=navy]Then[/COLOR]
       [COLOR=navy]If[/COLOR] Dic(Dn.Value).exists(.Range("A1").Offset(, ac).Value) [COLOR=navy]Then[/COLOR]
         Q = UCase(Dic(Dn.Value).Item(.Range("A1").Offset(, ac).Value))
          [COLOR=navy]If[/COLOR] Not Q = UCase(Intersect(Dn.EntireRow, .Range("A1").Offset(, ac).EntireColumn)) [COLOR=navy]Then[/COLOR]
                Intersect(Dn.EntireRow, .Range("A1").Offset(, ac).EntireColumn).Font.Color = vbRed
          [COLOR=navy]End[/COLOR] If
        [COLOR=navy]End[/COLOR] If
     [COLOR=navy]End[/COLOR] If
   [COLOR=navy]Next[/COLOR] ac
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Kinjal Doshi]

Hi Mick,

As soon as I paste this... it highlights below line.... and when I run the macro it says Syntax error

If Not IsEmpty(.Range("A1").Offset(, ac).Value) The


Also just wanted to know... If my columns increase... can I change the number 39 to 50 in macro manually?... will it run?

Awaiting your reply

Regards
Kinjal

 

[MickG]

When tidying the code I deleted an "n", at the end of "Then"

If Not IsEmpty(.Range("A1").Offset(, ac).Value) [B]Then[/B]

Also just wanted to know... If my columns increase... can I change the number 39 to 50 in macro manually?... will it run?

You need to Change the number 39, to 50 in 2 place.

 

[Kinjal Doshi]

Hi Mick,

Started working... but when it also shows me below new error when I run in other file.. .My first file ran fine... and when I changed my data it gave below error

"This key is already associated with an element of this collection." ---- What does this mean?


Awaiting your soonest reply
Kinjal

 

[MickG]

Can you send me a copy of the file/data it fails on.

 

[Kinjal Doshi]

Hi Mick,

Yes... please find it in below link... when you run the macro.. it will throw the error.

https://app.box.com/s/m6y3giuuxhguc54kigzw8cw2tkmc1udh


Actually sometimes I have to compare with three set of sheets... My first sheet has around 1000 lines data and out of that I have to compare the difference in only 500 lines data which is in my second sheet.... then I have to compare my second sheet data of 500 lines with my third sheet which is similar to that of second one... but there are typo errors and this is when it throws me this error... where my ID are similar in both sheets... hope you can find solution to this error.

Awaiting your reply
Kinjal

 

[MickG]

Try adding the 2 lines below shown in red:-

For ac = 1 To 39
[B][COLOR=#ff0000]                    If Not IsEmpty(.Range("A1").Offset(, ac).Value) Then[/COLOR][/B]
                        Dic(Dn.Value).Add .Range("A1").Offset(, ac).Value, _
                        Intersect(Dn.EntireRow, .Range("A1").Offset(, ac).EntireColumn)
[B][COLOR=#ff0000]                    End If[/COLOR][/B]
                Next ac

 

[Kinjal Doshi]

Hi Mick,

It worked in my sample file... but not in my original file... it shows same error.
what could be the reason? please advise


Awaiting your reply
Kinjal

 

[Kinjal Doshi]

Hi Mick,

It highlights below 2 lines...when I Debug it...

Dic(Dn.Value).Add .Range("A1").Offset(, ac).Value, _
Intersect(Dn.EntireRow, .Range("A1").Offset(, ac).EntireColumn)


Kinjal

 

[MickG]

Can you post your File. PM me with it, if necessary