I have an Excel file that has columns A and B, both have data that may or may not exist in other column, I'm only interested in the data of column A that do not exist in B. These Columns have same Header names. What formula can I use to show which items in column A aren't in B?
This question is related to
excel
excel-2007
worksheet-function
All values of column A that are not present in column B will have a red background. Hope that it helps as starting point.
Sub highlight_missings()
Dim i As Long, lastA As Long, lastB As Long
Dim compare As Variant
Range("A:A").ClearFormats
lastA = Range("A65536").End(xlUp).Row
lastB = Range("B65536").End(xlUp).Row
For i = 2 To lastA
compare = Application.Match(Range("a" & i), Range("B2:B" & lastB), 0)
If IsError(compare) Then
Range("A" & i).Interior.ColorIndex = 3
End If
Next i
End Sub
Suppose you have data in A1:A10 and B1:B10 and you want to highlight which values in A1:A10 do not appear in B1:B10.
Try as follows:
Enter the following formula:
=ISERROR(MATCH(A1,$B$1:$B$10,0))
Now select the format you want to highlight the values in col A that do not appear in col B
This will highlight any value in Col A that does not appear in Col B.
Source: Stackoverflow.com