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.

Ask a question
Report

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

dalami81
4Posts
Friday August 27, 2010Registration date
October 14, 2011Last seen
- Latest answer on Oct 17, 2011 05:32AM

Read more

- How to find missing data in two columns in excel
- Compare two columns and find missing values excel
- Compare columns of names in excel and make a list of missing values
- Excel - Compare columns to find missing data » How-To - Excel
- Excel - Comparing two columns A&B » How-To - Excel
- Compare cells of two columns excel 2007 (Solved) » Forum - Excel
- Extract identical cells of 2 columns (excel) » Forum - Excel
- Checking data in 2 columns and sending email » Forum - Excel

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;"")

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;"")

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