Excel array and vba

Operative workd - quickly!

You could do a simple if statement using match to determine this.  Do you really need a macro?

See attached.

the fundamental statement - in helper columns D (in A but not B) and E (in B but not in A) would be - for column a:

=IF(ISERROR(Match(A5,B:B,0)),1,""))

This would flag a 1 in column D that you could filter on with data filter.

See attached, with formulas for both situations.

Dave
inAnotB-r1.xls

the above posting assumes the data starts in A5 (just change to A2 or A1, based on your needs).  Of course, you'd copy the formula down for as much as you have data.

Happy checking!

Dave

I need to have a macro instead of copy, drag and paste.  

Nice one Dave,

You beat me to it and your solution looks better than mine* but I'll put up an offering anyway...
*(esp full column references instead of my hardcoded ranges)

select the populated cells in column A by choosing cell A1 & pressing [ctrl + shift + [down arrow]], press [alt + o + d], [alt + n], choose "Use a formula to choose which cells to format", then type the below equation in (change the "$15" to the bottom row number as needed). Press [Apply] & [ok] all the way out.

=COUNTIF($B$1:$B$15,$A1)=0

Select allOpen in new window

Now do the same for column B...
select the populated cells in column B by choosing cell B1 & pressing [ctrl + shift + [down arrow]], press [alt + o + d], [alt + n], choose "Use a formula to choose which cells to format", then type the below equation in (change the "$15" to the bottom row number as needed). Press [Apply] & [ok] all the way out.

=COUNTIF($a$1:$a$15,$b1)=0

Select allOpen in new window

Now, optionally... If you haven't already got an autofilter on, select cell A1:B1 & press [ctrl + shift + [down arrow] untion all the data rows are selected, then press [alt + d + f + f], now press [alt + [down arrow]], type "i" and choose the colour to filter for from the Filter dropdown.

hth
Rob

Ooopps, I really am too slow - Dave, I'll leave this one for you :-)

Rob

Ok - very quick so don't critique my code - going home!!!

See attached - works for activeworkbook and activesheet of that workbook.

See macro button on demo.

Compares A with B by reading both columns in and comparing A-side flagging no-matches with red fill color, then does the B-side flagging no-matches with red.  Afterward, you can use data filter to filter on that color zooming in if you have lots of data.

Here's the code:
 

Sub inAnotB()
Dim wkb As Workbook
Dim wks As Worksheet
Dim varA As Variant, varB As Variant

    Set wkb = ActiveWorkbook
    Set wks = wkb.ActiveSheet
    
    varA = Application.Transpose(wks.Range("A1", wks.Range("A" & wks.Rows.Count).End(xlUp)))
    varB = Application.Transpose(wks.Range("B1", wks.Range("B" & wks.Rows.Count).End(xlUp)))
    
    For i = LBound(varA) To UBound(varA)
        foundmatch = False
        For j = LBound(varB) To UBound(varB)
            If varA(i) = varB(j) Then
                foundmatch = True
                Exit For
            End If
        Next j
        If Not foundmatch Then wks.Range("A" & i).Interior.Color = vbRed
    Next i
    
    For i = LBound(varB) To UBound(varB)
        foundmatch = False
        For j = LBound(varA) To UBound(varA)
            If varB(i) = varA(j) Then
                foundmatch = True
                Exit For
            End If
        Next j
        If Not foundmatch Then wks.Range("B" & i).Interior.Color = vbRed
    Next i
End Sub

Select allOpen in new window

See attached.

Enjoy!

Dave
inAnotB-r1.xls

Thank you Dave!!!  This solution worked perfectly and thank you for the comments so that I could follow!!

Nice one Dave :-)

Rob