Find missing data in 2 columns (Excel)
Closed
dalami81
Posts
2
Registration date
Friday August 27, 2010
Status
Member
Last seen
August 27, 2010

Aug 27, 2010 at 11:37 AM
dalami81  Oct 17, 2011 at 05:32 AM
dalami81  Oct 17, 2011 at 05:32 AM
Related:
 Excel compare two columns for missing values
 Excel find missing values in two columns  Best answers
 Vlookup to find missing data in 2 columns  Best answers
 Compare two worksheets and paste differences to another sheet  excel vba free download ✓  Excel Forum
 Messenger missing emojis ✓  Facebook Messenger Forum
 Excel date format dd.mm.yyyy  Guide
 Vlookup compare two columns ✓  Excel Forum
 Excel intersection of two columns  Excel Forum
2 responses
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;"")