Macro comparing 2 columns in 2 sheets

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180
Example data:

sheet1
1 | a
1 | b
2 | a
2 | b

sheet2
1 | a
1 | b
1 | c
2 | a
2 | b
3 | a

the important part is the first column (those with numbers) and checking second column (those with alphabet). from the above sample data, should highlight "1 | c" in sheet2 as missing. somehow, ignore '3 | a' and do nothing.

thanks for helping.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Can you explain how "1 | c" is missing, but "3 | a" should be ignored? They are both in Sheet2 but not in Sheet1, so what is the logical rule that is applied.
 

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180
"1 | c" is considered missing because 1 is in both sheets, so we check the second column.

'3 | a" is being ignored because 3 is not in sheet1, so we can skip checking.

Btw, I forgot to mention that the real data is not sorted, not like the sample data I posted which is ascending sorted.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try
Code:
Sub test()
Dim dic As Object, r As Range
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1")
     For Each r In .Range("a1",.Range("a" & Rows.Count).End(xlUp))
        If Not IsEmpty(r) Then
            If Not dic.Exists(r.Value) Then
                dic.add r.Value, Nothing
                dic.add r.Value & r.Offset(,1).Value, Nothing
            End If
        End If
      Next
End With
With Sheets("Sheet2")
      .Columns("a").Interior.ColorIndex = xlNone
      For Each r In .Range("a1",.Range("a" & Rows.Count).End(xlUp))
         If Not IsEmpty(r) Then
             If dic.exists(r.Value) Then
                 If Not dic.exists(r.value & r.Offset(,1).value) Then
                      r.Interior.Color = vbRed
                 End If
             End If
          End If
       Next
End With
Set dic = Nothing
End Sub
 

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180

ADVERTISEMENT

Run-time error '450':
Wrong number of arguments or invalid property assignment
Code:
dic.Add r.Value & r.Offset(, 1).Value
 

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180

ADVERTISEMENT

Using my sample data as in the first post, it wrongly highlighted:
1 | b
1 | c
2 | b

It suppose to highlight only "1 | c"

Besides that, can we make it highlighting the whole row?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try this one
Code:
Sub test()
Dim dic As Object, r As Range
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1")
     For Each r In .Range("a1",.Range("a" & Rows.Count).End(xlUp))
        If Not IsEmpty(r) Then
            If Not dic.Exists(r.Value) Then
                dic.add r.Value, Nothing
                dic.add r.Value & r.Offset(,1).Value, Nothing
            Else
                If Not dic.exists(r.Value & r.Offset(,1).Value) Then
                    dec.add r.Value & r.Offset(,1).Value
                End If
            End If
        End If
      Next
End With
With Sheets("Sheet2")
      .Cells.Interior.ColorIndex = xlNone
      For Each r In .Range("a1",.Range("a" & Rows.Count).End(xlUp))
         If Not IsEmpty(r) Then
             If dic.exists(r.Value) Then
                 If Not dic.exists(r.value & r.Offset(,1).value) Then
                      r.EntireRow.Color = vbRed
                 End If
             End If
          End If
       Next
End With
Set dic = Nothing
End Sub
 

Forum statistics

Threads
1,141,027
Messages
5,703,818
Members
421,318
Latest member
cg_cartoonexcel

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