Sheet Comparisons

[rudogg]

Fluff provided some great code in the thread below!!!

https://www.mrexcel.com/board/threads1112978/

I have modified it a bit and I need some extra direction on it's functionality, if anyone could be so kind.

Sub Match_Our_Price()
    Dim Cl As Range, mydiffs As Integer
    Dim Dic As Object
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Sheet3")
        For Each Cl In .Range("A2", .Range("A" & Rows.count).End(xlUp))
            Dic(Cl.Value) = Cl.Offset(, 1).Value
        Next Cl
    End With
    With Sheets("Sheet1")
        For Each Cl In .Range("BX2", .Range("BX" & Rows.count).End(xlUp))
            If Dic.exists(Cl.Value) Then Cl.Offset(, -58).Value = Dic(Cl.Value)
            mydiffs = mydiffs + 1
        Next Cl
    End With
    'Display a message box to demonstrate the differences
    MsgBox mydiffs & " Our Price Fields (Column R) have been Changed.", vbInformation
End Sub

So Sheet 1 is the master sheet, in which the pricing data is being updated. Sheet 3 is the data being copied from the current pricelist sent by the manufacturer. Just 2 columns for now. Column A is the MPN and column B is the Updated Price. So, if Sheet1-Column BX, exists on Sheet 3-Column A, Color the cell Green on Sheet 1, and if Sheet 3-Column B is Different than Sheet 1-Column R, then Copy Sheet 3-Column B to Sheet 1-Column R and color the cell Blue on Sheet 1.

I tried to add a message box displaying how many fields where changed but it just counted every single row on Sheet1. Thank you in advance for any help you could provide.


Sheet 1 Data

Sheet 3 Data

 

[Fluff]

How about

Sub Match_Our_Price()
    Dim Cl As Range, mydiffs As Integer
    Dim Dic As Object
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Sheet3")
        For Each Cl In .Range("A2", .Range("A" & Rows.count).End(xlUp))
            Dic(Cl.Value) = Cl.Offset(, 1).Value
        Next Cl
    End With
    With Sheets("Sheet1")
        For Each Cl In .Range("BX2", .Range("BX" & Rows.count).End(xlUp))
            If Dic.Exists(Cl.Value) Then
               Cl.Interior.Color = 64636
               If Cl.Offset(, -58).Value <> Dic(Cl.Value) Then
                  Cl.Offset(, -58).Value = Dic(Cl.Value)
                  Cl.Offset(, -58).Interior.Color = 16760576
               End If
               mydiffs = mydiffs + 1
            End If
        Next Cl
    End With
    'Display a message box to demonstrate the differences
    MsgBox mydiffs & " Our Price Fields (Column R) have been Changed.", vbInformation
End Sub

 

[rudogg]

How about

Sub Match_Our_Price()
    Dim Cl As Range, mydiffs As Integer
    Dim Dic As Object
   
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Sheet3")
        For Each Cl In .Range("A2", .Range("A" & Rows.count).End(xlUp))
            Dic(Cl.Value) = Cl.Offset(, 1).Value
        Next Cl
    End With
    With Sheets("Sheet1")
        For Each Cl In .Range("BX2", .Range("BX" & Rows.count).End(xlUp))
            If Dic.Exists(Cl.Value) Then
               Cl.Interior.Color = 64636
               If Cl.Offset(, -58).Value <> Dic(Cl.Value) Then
                  Cl.Offset(, -58).Value = Dic(Cl.Value)
                  Cl.Offset(, -58).Interior.Color = 16760576
               End If
               mydiffs = mydiffs + 1
            End If
        Next Cl
    End With
    'Display a message box to demonstrate the differences
    MsgBox mydiffs & " Our Price Fields (Column R) have been Changed.", vbInformation
End Sub

Holy Cow dude!!! Fluff, You Nailed It! The only thing I changed was nesting mydiffs count in the level above so it only counted the price changes. Thank you kindly!!!!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[rudogg]

Can the option of special characters be ignored on either sheet if necessary as well? If TU-3 is on Sheet3 and TU3 is on Sheet1 can that be a match?

How about Leading zero's?

 

[Fluff]

Can the option of special characters be ignored on either sheet if necessary as well?

There is no in-built option for that.

 

[rudogg]

There is no in-built option for that.

Bummer. I'll just have to preformat my data, and then do 2 separate compares.. Still Much less work with your code! Thank you for responding..

 

[rudogg]

I have another question regarding this code. I am trying to use it in the same fashion for matching GTIN numbers on sheets 1 and 3.

The GTIN's on Sheet1 are formatted as General, so as soon as I click in a field and hit enter it turns to the scientific display of the number.
The GTIN's on Sheet3 are formatted as General sometimes, and as custom 000000000000 in other cases.

I can't really modify the formats on Sheet1 for fear of data not being reimported.

How do I compare these types of numbers?

 

[Fluff]

As that is a totally different question, it needs a new thread. Thanks

 

[rudogg]

When creating the dictionary, is it possible to exclude empty cells from being indexed If Sheet3 Column A is Blank?