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 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)
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: