Compare two columns to see what is missing from one of them

**Emilk** · 06-24-2010, 05:40 PM

Hi, I'm working on traceability to make sure that we are testing all of the items we need to for a given project. We have identified a unique list of some 2000 items that have to be tested. As we have created test documentation we have recorded all of the items are covered in those tests to date.

I need to be able to compare column A (full list of 2000 items) with column B (items that are covered with tests), and see which items from column A have not had tests written for them yet. I'm assuming there's an easy way to do this via a formula in Excel, but can't seem to figur it out. Could anyone help me with this?

Thanks!

**zbor** · 06-24-2010, 05:46 PM

Try =COUNTIF(B:B, A1)

If result is 0 then that item is missing

**Paul** · 06-24-2010, 05:46 PM

Hi Emilk,

Assuming full list is in A1:A2000, and test list in B1:B500, in C1 use the formula:

=ISNUMBER(MATCH(A1,$B$1:$B$500,0))

Fill that down to C1000. Any cell in column C that has FALSE means the corresponding value from column A is missing from column B.

Or, rather than a bunch of TRUE/FALSE responses, to just show where missing values occur, in C1 use:

=IF(ISERROR(MATCH(A1,$B$1:$B$5,0)),"Missing","")

Fill down to C1000.