Excel array and vba

Hi Experts,

I have 2 columns in my excel 2007 worksheet.  I need to compare the values in column A to the values in column B.  If the values in column A are not present at all in column B, then I need to have the value that is not present in column B highlighted.  I also need to have the values that are in column B, but not in column A highlighted as well.  Can anyone help me with this quickly?
daintysallyAsked:
Who is Participating?
 
dlmilleCommented:
This code is now optimized, using the MATCH function, rather than iterating through arrays.

Code:
Sub inAnotB()
Dim wkb As Workbook
Dim wks As Worksheet
Dim matchValue As Long
Dim lastRowA As Long, lastRowB As Long

    Set wkb = ActiveWorkbook
    Set wks = wkb.ActiveSheet
    
    lastRowA = wks.Range("A" & wks.Rows.Count).End(xlUp).Row
    lastRowB = wks.Range("B" & wks.Rows.Count).End(xlUp).Row
    
    
    On Error Resume Next
    For Each mycell In wks.Range("A1:A" & lastRowA)
        If mycell.Value <> "" Then 'if nonblank cell, then compare
            matchValue = Application.WorksheetFunction.Match(mycell.Value, wks.Range("B1:B" & lastRowB), 0)
            If Err.Number <> 0 Then 'match not found
                mycell.Interior.Color = vbRed
                Err.Clear
            End If
        End If
    Next mycell
    
    For Each mycell In wks.Range("B1:B" & lastRowB)
        If mycell.Value <> "" Then 'if nonblank cell, then compare
            matchValue = Application.WorksheetFunction.Match(mycell.Value, wks.Range("A1:A" & lastRowA), 0)
            If Err.Number <> 0 Then 'match not found
                mycell.Interior.Color = vbRed
                Err.Clear
            End If
        End If
    Next mycell

End Sub

Open in new window


See attached.

Enjoy!

Dave
inAnotB-r2.xls
0
 
dlmilleCommented:
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
0
 
dlmilleCommented:
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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
daintysallyAuthor Commented:
I need to have a macro instead of copy, drag and paste.  
0
 
broro183Commented:
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

Open 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

Open 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
0
 
broro183Commented:
Ooopps, I really am too slow - Dave, I'll leave this one for you :-)

Rob
0
 
dlmilleCommented:
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

Open in new window


See attached.

Enjoy!

Dave
inAnotB-r1.xls
0
 
daintysallyAuthor Commented:
Thank you Dave!!!  This solution worked perfectly and thank you for the comments so that I could follow!!
0
 
broro183Commented:
Nice one Dave :-)

Rob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.