?
Solved

Excel array and vba

Posted on 2011-10-11
9
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
Comment
Question by:daintysally
  • 4
  • 3
  • 2
9 Comments
 
LVL 42

Expert Comment

by:dlmille
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

by:dlmille
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

by:daintysally
ID: 36953027
I need to have a macro instead of copy, drag and paste.  
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 10

Expert Comment

by:broro183
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

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
 
LVL 10

Expert Comment

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

Rob
0
 
LVL 42

Expert Comment

by:dlmille
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

Open in new window


See attached.

Enjoy!

Dave
inAnotB-r1.xls
0
 
LVL 42

Accepted Solution

by:
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

Open in new window


See attached.

Enjoy!

Dave
inAnotB-r2.xls
0
 

Author Closing Comment

by:daintysally
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

by:broro183
ID: 36958760
Nice one Dave :-)

Rob
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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…

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.

Join & Ask a Question