VBA - Checking for duplicates across multiple cells

jxspea

New Member
Joined
Jul 17, 2014
Messages
13
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

Does this code work as expected?

Code:
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
[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

New Member
Joined
Jul 17, 2014
Messages
13
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
[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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,548
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top