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 formula to find missing data between two columns
 Vlookup to find missing data in 2 columns  Best answers
 Excel find missing values between two columns  Best answers
 Position formula in excel ✓  Office Software Forum
 Date formula in excel dd/mm/yyyy  Guide
 Number to words in excel formula  Guide
 Transfer data from one excel worksheet to another automatically  Guide
 How to calculate position (1st,2nd,3rd) in excel....?  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;"")