Find Missing Data Using COUNTIF Nested in IF Function

Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010





In Column A I have a list of cities in which my customers reside. In column B I have tried to note down all the cities where my customers reside but I am sure I have missed some because the list is quite long.  How can I extract the city names from column B which I have missed without searching for them manually? Is there a function for that? Thanks :)!
The user most probably means how to find the missing city names from column A. We do this as follows:
  1. Using the COUNTIF function we can determine which cities the user has noted down correctly. If the formula gives us the value 1 then we can be sure that the cities he has noted down in column B are also available in column A. The ones with the result 0 are the missing ones
  2. Using this knowledge we can put an IF function before the cCOUNTIF function so that all the values greater than 0 are ignored and all the values equal to 0 or the missing cities appear in our results. Therefore our final formula takes the form =if(countif(B:B,A1)>0,"",A1)
  3. The missing cities have now been found. In fact, this is exactly the opposite use of the countif function where we used it to find duplicates
Watch the video below to learn how to find the missing data using a countif function nested in a IF function:



Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010