Find all items on list B that are not on list A, Excel.

Question

I have list A with 259 items and list B with 626 items. There is some overlap between the list, so I want to find all items on list B, which are not on list A. I've been trying to find some info on how to do it in Excel on the Net, but I cant figure it out. Would you be able to give me the best and the easiest way of doing it, and also explained on how to do it (like people say "copy the formula down to all the rows", but I am not sure how would I do this).

Thanks.

Solution 1

List A is in column A. List B is in column B. Data starts in row 1. Put this formula into cell C1

=iferror(match(B1,$A$1:$A$259,0),"not in list A")

Select cell C1. Notice the little square in the lower right-hand corner. That is the fill handle. Double click the fill handle to copy the formula all the way down to cell B626.

Another possible formula, as suggested in the comments, can be constructed with Countif(). Something along the lines of

=if(Countif($A$1:$A$259,B1),"","not in list A")

Again, enter in C1, then use the fill handle to fill down.

Solution 2

A powerful way of doing this is to use Excel Power Query.

See this blog for a how-to: https://www.ablebits.com/office-addins-blog/2018/11/14/excel-join-tables-power-query/

The article above walks you through how to perform a merge of two data sets and then choose a Left-Outer (or Left-Anti join depending on what you want in the results).

And see this Microsoft article to find out more about Power Query in general: https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a