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:
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
Using this knowledge we can put an IF function before the COUNTIF 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)
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: