# Find missing data in 2 columns (Excel) [Closed]

Ask a question dalami81 2Posts Friday August 27, 2010Registration date August 27, 2010 Last seen - Last answered on Oct 17, 2011 at 05:32 AM by dalami81
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.

+26
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;"")
+1
A two column solution might be:
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.

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.