26

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?

paulmorriss
  • 2,579
  • 25
  • 30
Tim
  • 582
  • 2
  • 6
  • 12
  • 1
    See [excel-compare-two-columns-matches-differences](https://www.ablebits.com/office-addins-blog/2015/08/26/excel-compare-two-columns-matches-differences/) – Michael Freidgeim Jun 21 '16 at 00:17

3 Answers3

51

Put this in C2 and copy down

=IF(ISNA(VLOOKUP(A2,$B$2:$B$65535,1,FALSE)),"not in B","")

Then if the value in A isn't in B the cell in column C will say "not in B".

paulmorriss
  • 2,579
  • 25
  • 30
  • for newer versions of MS Excel " , " is replaced by " ; " =IF(ISNA(VLOOKUP(A2;$B$2:$B$65535;1;FALSE));"not in B";"") – Tim Jan 04 '21 at 12:27
7

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:

  1. Format > Conditional Formating...
  2. Select 'Formula Is' from drop down menu

  3. Enter the following formula:

    =ISERROR(MATCH(A1,$B$1:$B$10,0))

  4. 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.

Alex P
  • 12,249
  • 5
  • 51
  • 70
  • +1 Nice solution. I've tested it right now but it seems to me that you have to put the range of b column (something like $b$1:$b$10) as second parameter. – Nicola Cossu Apr 27 '11 at 13:23
  • @nick rulez - Good spot. You are correct. I have updated answer accordingly. – Alex P Apr 27 '11 at 13:50
4

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
Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98