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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,992
Messages
5,545,375
Members
410,679
Latest member
rolandbianco
Top