Ask a question Report

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

dalami81 2Posts Friday August 27, 2010Registration date August 27, 2010Last seen - Latest answer on Oct 17, 2011 05:32AM
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.
Read more 
Answer
+26
moins plus
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;"")

Answer
+1
moins plus
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.

This document entitled « Find missing data in 2 columns (Excel) » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.