Solved

# Excel array and vba

Posted on 2011-10-11
Medium Priority
220 Views
Last Modified: 2012-05-12
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?
0
Question by:daintysally
• 4
• 3
• 2
9 Comments

LVL 42

Expert Comment

ID: 36952998
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

LVL 42

Expert Comment

ID: 36953000
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

Author Comment

ID: 36953027
I need to have a macro instead of copy, drag and paste.
0

LVL 10

Expert Comment

ID: 36953048
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
``````

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
``````

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

LVL 10

Expert Comment

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

Rob
0

LVL 42

Expert Comment

ID: 36953100
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
``````

See attached.

Enjoy!

Dave
inAnotB-r1.xls
0

LVL 42

Accepted Solution

dlmille earned 2000 total points
ID: 36953297
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
``````

See attached.

Enjoy!

Dave
inAnotB-r2.xls
0

Author Closing Comment

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

LVL 10

Expert Comment

ID: 36958760
Nice one Dave :-)

Rob
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month15 days, 12 hours left to enroll

#### 850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.