Find missing data in 2 columns (Excel)Closed

Question

Hello, 

I have two lists of data (patent serial numbers) that I need to compare and find the missing serial numbers.  In Column A I have my database's list of patent serial numbers. In Column B I have the patent office's list of my patent serial numbers. Theoretically these lists should be identical, but they are not.

What I need to do is find what serial numbers are missing displayed in Column C.  It would be helpful if the results in Column C were shaded to represent if the number came from Column A or Column B, but it is not critical.

I have run several macros but they are not providing the results I am seeking.  Any help is appreciated!  Thanks.

Configuration: Windows 7 / Firefox 3.6.8

dalami81 · Accepted Answer

Assuming the list in column B is shorter than in column A, write the following formula in the C1 cell: =IF(COUNTIF(A:A;B1)=0;B1;"")
This formula searches through the A column for the B1 value. If it doesn't find anything (COUNFIF is equal to 0) means the B1 is in B but missing in A. It will write the B1 value in the C1 cell. Otherwise it will leave the cell empty.
Copy this formula in the entire C column. You will probably have to replace ; with an ordinary comma but in Excel 2003 it works only with ;
You can do it the other way around in column D to see if some values from A are in A but not in B. =IF(COUNTIF(B:B;A1)=0;A1;"")

Ray · Answer

A two column solution might be:
In column A, your number
In colunm B, their number
in column C
=IF(ISBLANK(A2)," ",VLOOKUP(A2,B$2:B$34,1,FALSE))
in column D
=IF(ISBLANK(B2)," ",VLOOKUP(B2,A$2:A$34,1,FALSE))

change the $34 to be how many rows you are comparing.
The isblank function is there to stop the comparison if the source cell is empty.

Find missing data in 2 columns (Excel)

2 responses

Similar discussions

Subscribe To Our Newsletter!