VBA - Checking for duplicates across multiple cells

[jxspea]

Hello all,

I am having to check a large amount of data and am trying to automate a part of my process to help tone down my checking time. I believe that VBA is the best way to handle what I am trying to accomplish, and so I have been reading tutorials/watching youtube videos over basic concepts. I even enrolled in a course at my local community college! You guys who program are extremely valuable in your companies, and I am trying to increase my value as well.

With that being said I don't know much (if anything, to be honest), and I was hoping that someone could point me in the right direction of how I can go about accomplishing my task.

What I have is a spreadsheet that looks similar to the one below.

A	B	C	D	E	F
G_WELD	CMLG0374	0330 034 13A	16.5	0322 034 05A	30.2
G_WELD	XMLS0378	2000679677	44.95	0330 034 13A	16.5
P_TRANS	0.375	X60	0.312	X60
L_END	9099568
G_WELD	CMLG0376	0177 034 05	64.6	0123 054 12	44.98

<tbody>
</tbody>

Column A is a code. I will have multiple codes in one spreadsheet, and the order that they appear will be at random. Each code has its own set of attributes, and they populate in B:F.

Right now I am focusing on G_WELD, because if I can figure out how to do that one code I believe I can figure out the rest.
Each time G_Weld pops up in Column A, the code will need to compare its C column against the E column of the NEXT G_WELD. Please notice how C1 and E2 are the same. If the contents of the cells are different, then they need to be highlighted, as is the case for C2 & E5. The problem is there might be multiple codes in column A between each G_WELD.

Originally I filtered my spreadsheet by Column A, and then did a conditional formatting once all my G_WELDS were together, but that was a mistake on my end, because I have to keep the order of the codes as they are exported from the software we use. So it makes checking them very tedious.

So my question is, can I make my active cell be A, have code that runs down the columns until it hits a G_WELD, then offset the active cell towards C, have it store that information, and check it against the column E of the NEXT G_WELD in column A?


Sorry if my question is poorly worded. I appreciate your patience and your time in reading my question. Thank you very much for any help!

Jxspea

 

[onlyadrafter]

Hello,

Does this code work as expected?

Sub COMPARE()
    For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("A" & MY_ROWS).Value = "G_WELD" Then
            MY_COL_C = Range("C" & MY_ROWS).Value
            For MY_NEXT_ROWS = MY_ROWS + 1 To Range("A" & Rows.Count).End(xlUp).Row
                If Range("A" & MY_NEXT_ROWS).Value = "G_WELD" Then
                    If Range("E" & MY_NEXT_ROWS).Value <> MY_COL_C Then
                        Range("C" & MY_ROWS).Font.Color = vbRed
                        Range("E" & MY_NEXT_ROWS).Font.Color = vbRed
                    End If
                    GoTo CHECKED_NEXT
                End If
            Next MY_NEXT_ROWS
        End If
CHECKED_NEXT:
    Next MY_ROWS
End Sub

Can re-code it for all col A values if you require.

 

[MickG]

Try this:-

[COLOR=Navy]Sub[/COLOR] MG30May54
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] K [COLOR=Navy]As[/COLOR] Variant, Temp [COLOR=Navy]As[/COLOR] Range, G [COLOR=Navy]As[/COLOR] Range
    [COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
        [COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .keys
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] G [COLOR=Navy]In[/COLOR] .Item(K)
        [COLOR=Navy]If[/COLOR] Not Temp [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]If[/COLOR] Not Temp.Value = G.Offset(, 4).Value [COLOR=Navy]Then[/COLOR]
                Temp.Font.ColorIndex = 3
                G.Offset(, 4).Font.ColorIndex = 3
            [COLOR=Navy]End[/COLOR] If
         [COLOR=Navy]End[/COLOR] If
                [COLOR=Navy]Set[/COLOR] Temp = G.Offset(, 2)
    [COLOR=Navy]Next[/COLOR] G
[COLOR=Navy]Set[/COLOR] Temp = Nothing
[COLOR=Navy]Next[/COLOR] K
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[jxspea]

This is brilliant, thank you! I really appreciate you taking the time. I can use this format for my other codes as well. One question for you sir - say I wanted to compare both C&D against E&F. Couldn't I just add & "D" everywhere I have "C" to encompass that other column? I tried that and it didn't work. Sorry if I worded that question incorrectly. Regardless, this is fantastic - thank you!

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG30May00
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Temp2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, Temp [COLOR="Navy"]As[/COLOR] Range, G [COLOR="Navy"]As[/COLOR] Range
    [COLOR="Navy"]Set[/COLOR] rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
      rng.Resize(, 6).Font.ColorIndex = 1
        [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]


[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] G [COLOR="Navy"]In[/COLOR] .Item(K)
        [COLOR="Navy"]If[/COLOR] Not Temp [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Not Temp.Value = G.Offset(, 4).Value [COLOR="Navy"]Then[/COLOR]
                Temp.Font.ColorIndex = 3
                G.Offset(, 4).Font.ColorIndex = 3
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]If[/COLOR] Not Temp2 [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
           [COLOR="Navy"]If[/COLOR] Not Temp2.Value = G.Offset(, 5).Value [COLOR="Navy"]Then[/COLOR]
               Temp2.Font.ColorIndex = 3
               G.Offset(, 5).Font.ColorIndex = 3
           [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]Set[/COLOR] Temp2 = G.Offset(, 3)
                [COLOR="Navy"]Set[/COLOR] Temp = G.Offset(, 2)
    [COLOR="Navy"]Next[/COLOR] G
[COLOR="Navy"]Set[/COLOR] Temp = Nothing
[COLOR="Navy"]Set[/COLOR] Temp2 = Nothing
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick