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
- Excel grade formula - Guide
- Number to words in excel formula - Guide
- Display two columns in data validation list but return only one - Guide
- 1st, 2nd, 3rd position formula in excel ✓ - Office Software Forum
- Date formula in excel dd/mm/yyyy - Guide
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;"")