You can use an object known as a dictionary. A dictionary has a key and a corresponding value. The way it stores the key is implemented so that it is quick to find out if a particular key exists in the dictionary, which will be helpful in checking for duplicate product codes.
For simplicity (and because I have little experience of VBA in Excel), I have assumed the data to check is in columns 1 and 2 of Sheet(1):
Option Explicit
' Add reference to get Dictionary: http://www.techbookreport.com/tutorials/vba_dictionary.html
' Excel VBA- Finding the last column with data: https://stackoverflow.com/a/11927387
Sub FindDuplicates()
Dim dict As Dictionary
Set dict = New Dictionary
Dim ws As Worksheet
Set ws = Sheets(1)
Dim rLastCell As Range
Set rLastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
Dim key As String
Dim val As String
Dim dupes As String
Dim i As Long
' use maxFinds to limit the number of duplicates found
Dim maxFinds As Integer
maxFinds = 32
Dim nFound As Integer
nFound = 0
For i = 1 To rLastCell.Row
key = Cells(i, 1).Value
val = Cells(i, 2).Value
If dict.Exists(key) Then
If dict(key) <> val Then
dupes = dupes & "Row: " & i & " Class: " & val & vbCrLf
nFound = nFound + 1
End If
Else
dict.Add key, val
End If
If nFound = maxFinds Then
Exit For
End If
Next
If nFound = 0 Then
MsgBox ("No duplicates found.")
Else
MsgBox (dupes)
End If
End Sub
The dictionary object isn't built-in to Excel, so you need to add a reference to Microsoft Scripting Runtime through "Tools" menu -> "References...".
I had created a test file with 50,000 rows and quite a lot of duplicates which is why the code ended up with a facility to limit the number of duplicates found, so you could set maxFinds = 10 and go through those, then run the macro again to find another ten, and so on. Also, if there are many more than 32-ish then they don't fit in the message box anyway.
It assumes that the first occurence of a "Product Class" (or the value in column 2 in the above code example) is the correct one.
Example output:
