compare between 2 columns to find Missing Names and put it in new column

[tgh12]

Hello guys,

I need a formula in excel to compare between 2 columns to find Missing Names and put the missing names in new column called Missing Val

[Limor_OP]

Hi
Maybe this in E2 and down:
=IFERROR(INDEX($B$1:$B$79,AGGREGATE(15,6,(ROW($B$3:$B$79)/(((COUNTIF($H$3:$H$56,$B$3:$B$79)=0)))),ROWS($A$1:A1))),"")

[CARACALLA]

E2=IFERROR(INDEX($B$3:$B$79,SMALL(IF(ISNA(MATCH($B$3:$B$79,$H$3:$H$56,0)),ROW(B$3:$B$79)-ROW($B$3)+1),ROWS($B$3:B3))),"")

Control+shift+enter

copy down

[tgh12]

ok its work many thanks
can you explain the formula please ?
because I am beginner level in excel

[alansidman]

Attached you will find your file with the missing names shown. This was accomplished loading both tables into Power Query and joining the tables in an anti-join. This join shows only the names that are in the first table and not in the second..

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

[tgh12]

ok its work many thanks , however I don't have any experience in power query

[alansidman]

I understand. That is why I gave you links to read and learn about PQ. It is a very powerful piece of Excel that makes these type of issues very easy. If you really want to learn, then pick up a copy of "M Is for (Data) Monkey" a primer by Ken Puls and Miguel Escobar. Amazon is where you will find it.