LinkBack URL
About LinkBacks
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
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
Last edited by tgh12; 02-04-2021 at 02:43 PM. Reason: add more explination
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))),"")
ok its work many thanks
can you explain the formula please ?
because I am beginner level in excel
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
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.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
ok its work many thanks , however I don't have any experience in power query
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks