daintysally
asked on
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?
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?
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
Happy checking!
Dave
ASKER
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.
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.
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
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
Ooopps, I really am too slow - Dave, I'll leave this one for you :-)
Rob
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:
See attached.
Enjoy!
Dave
inAnotB-r1.xls
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Dave!!! This solution worked perfectly and thank you for the comments so that I could follow!!
Nice one Dave :-)
Rob
Rob
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
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