Excel Magic Trick 1226: Compare 2 Lists, Extract Items In List 2 That are NOT in List 1 (6 Examples)
Вставка
- Опубліковано 13 сер 2015
- Download Excel Start File: excelisfun.net/files/EMT1226-...
Download File: excelisfun.net/
Learn how to Compare 2 Lists, Extract Items In List 2 That are NOT in List 1:
1. (00:12) Problem Description and Intro To Topic
2. (01:57) Example 1: Method 1: MATCH function Helper Column, Sort Z to A, Copy and Paste
3. (04:15) Example 2: Method 2: Advanced Filter and Criteria Area with Empty Field Name and Logical Formula Criteria
4. (07:59) Example 3: Method 3: Automat Process with Excel 2010 or later Array Formula. See the Functions: IF, ROWS, ROW, MATCH, ISNA, INDEX, AGGREGATE, SUMPRODUCT. This Array Formula does not require Ctrl + Shift + Enter.
5. (15:29) Why you should NOT use IFERROR on Data Extract Array Formulas
6. (17:17) Example 4: Method 3: Automat Process with Excel 2007 or before Array Formula. See the Functions: IF, ROWS, ROW, MATCH, ISNA, INDEX, SMALL, SUMPRODUCT. This Array Formula DOES require Ctrl + Shift + Enter.
7. (18:25) Example 5: Method 2: Extract E-mails in List 1 that are NOT in List 2.
8. (19:35) Caveat about Advanced Filter: Be sure to NOT have data below Extract Area (because it will be deleted when you run Advance d Filter.
9. (20:24) Example 6: How to Extract Items that are in BOTH lists: Learn how to slight change the formula for all three methods. Use ISNUMBER rather than ISNA.
Related Videos for Comparing Two Lists and Extracting Records:
Other Excel Methods for Comparing Two Lists:
Excel Magic Trick 1226: Compare 2 Lists, Extract Items In List 2 That are NOT in List 1 (6 Examples)
• Excel Magic Trick 1226...
Excel Magic Trick 1229 Extract Items NOT in List 1: Conditional Format Unique Values & Sort by Color
• Excel Magic Trick 1229...
Excel Magic Trick 1441: Power Query to Extract Items In List 1 That Are NOT In List 2
• Excel Magic Trick 1441...
Excel Magic Trick 1442: DAX Query & EXCEPT Function to Extract Items In List 1 That Are NOT In List 2
• Excel Magic Trick 1442...
My favorite yet.
I usually just put my values in A, and a description in B like "firstlist" & "secondlist" then in C do a if countif formula. I really like your last solution.
Excellent. Used it to create a table to put values from a drop down menu where a value can be picked for only once. Thank you very much for your video.
Until now I have compated my two lists with the help of vlookup to and back. I loved the advanced filter solution for this issue, I will try it asap in real. Thank you Mike!
I like the way you *Booped Cell H39&40 with the format painter! :D
Advanced filter method - so cool!
And thanks for pointing out that deletion below the extracted list issue :)
pmsocho , Yes, that deletion below Extract Area is something Microsoft added, but told no one... In Earlier versions it did not do that...
Mega thx, that array was exactly what I needed
Amazing, awesome tips from girvin
+ everything is free from Mike.
thank u so much
Anwar from yemen
+Anwar Ahmed You are welcome!
The advanced filter method was great! Thanks Mike!
Kevin Lehrbass You are welcome!
ExcelIsFun I had never thought of using advanced filter! I was thinking "WHAT?!" So creative !!
Kevin Lehrbass Cool! Rad Creative Excel is fun!
Another great video, thanks a lot
Amazing training
Wow, great video mate, I had to figure this one out on my own... got it
Alan Douglas Glad you liked it!
Hi Mike....amazing as usual...!!
You're amazing! Thanks for these
Trey Stegall I am glad the the videos help!
OMG! Thanks so much!
Wish you made the dynamic array formula more robust by making the ranges dynamic i.e using index. and without any helper columns. Excellent video btw.
ACE Video, for learning the AGGREGATE Function, and ARRAYS.
Glad it was ACE for you, SanadFeisal!!!
Thank you.
Hi Mike Excellent video, I humbly add that could use a table (Ctrl + t) joining both lists names Column 1 , List Column2 (as List1 and List2), then make a Pivot Table rows by dragging names and names in values and use the count function, and use filter in row
Alejandro G. Messina It is amazing how many cool ways to do things in Excel! :) I really should have done a video with all the cool ways to do this! Thanks for the great solution!
Dude THANK YOU!!!!!
You are welcome!!!!!!
Thank you. This will help me in my work.
Jóhann Kristjónsson Glad it helps!
It is amazing. Thanks.
aknoimak You are welcome!
Many solutions for this! can also use IF(ISNA(VLOOKUP)) then filter
Briton Wells , It is amazing how many cool ways to do things in Excel! :)
I think i might save a lot of time ☺️
Dang ! This was made 6 years ago 😲
Hi Mr. ExcellsFun, do you know if we can compare 2 cells when they have multiple line in a cell? They are entered with alt+enter.
great!
Glad you like it!
Thank you for all your videos. You are a great trainer.
Please how can I Compare two lists A and B in different workbooks? List A is the standard. If there are differences in List B, we copy the correct value from A to list B.
Thank you for showing. After I entered the formulas I get rows which are less then my count number but are returning zeros. What can I add to formula to remove zeros?
Nice! Just looks like a bit involved. I compare lists almost on a daily basis and put them side by side and just use Conditional Formatting, Unique Values, then sort via color and send the colored ones to the top (depending on the column I want to get the values for).
Manaz101 It is amazing how many cool ways to do things in Excel! :)
Manaz101 i do the same :) simple and elegant
+Yuvaraj Krishnamoorthi Yes, I even created a macro to do it, I just copy/paste the 2 columns how I need them and run the macro. :)
Manaz101 and Yuvaraj Krishnamoorthi Your method of Conditional Formatting, Unique Values, Sort is very cool! I will have to make a video and dedicate it to you two Excel Experts!!!!
ExcelIsFun Not needed Mike, just glad to share what I learned along the way!
Love your videos!
Very cool, what do I do if the original list keeps growing?
Dear Mr.Excel,
In regarding to MOD function, I once saw someone use it with IF function as =IF(MOD(G35,1),"equip","other"), this formula was used to differentiate equipment(code ending with .05) from other goods(coded as whole numbers). I can understand that when a whole number is divided by 1, the remainder will be 0, which viewed as false by excel, therefore it will return "other", however when the referenced cell is a code ending with .05, why the formula can return "equip", as I understand only 1=true in excel....
Anything wrong about my understanding in regarding to this?
Thanks
Hi ExcellsFun, I'm new to excel... I have been forced by my current job to learn excel hehehe... would the ISNA(MATCH....)) also help with numeric values? Thank you in advance!
Dear Mr. Excel,
In CF(32), to express between lower and upper date, I was trying to write B3
Hi I have invoice no with multiple collection amount how to know the exact value of outstanding on the invoice no.
Hi Mr excellsfun do you know how to compare two worksheet where you can overpopulate data that is not on the other set of data having duplicate values
Great work Mike, thanks.
What if e-mails in List 2 are to be extracted and pasted into the column (Col B) adjacent to the matching e-mail address (Col A = List 1) leaving the unmatched e-mail addresses in List 2 (in say Col D) which can then be moved (manually) into the next empty row in Col B at the bottom of List 2?
Kerry King , I do not know, because I do not understand what you are asking. But I am not that smart at interpreting people's questions.
I think it's give error so no reference
Hey can share me a link of all of your magic trick video so that I can watch every video ?
Mind blowing! How long did it take you to become this proficient? You flow like if you were playing a piano...
Wow!!! I love that simile!!! Yes, it is like an art to me, like playing the piano, like dancing, like racing my bike (one of my hobbies).
Thanks for your support, Emilio!!!
Second comment, I have 6 or so columns of data that needs to be checked against another list and if the name on list is not in any columns of data then I want it to show. Your formula works to check one column of data but how would I check multiple.
Thanks for video!
I have one question though: why does criteria header in advanced filter have to remain empty?
Arkadiusz Urban I am not sure the exact reason, but that is the method that I learned years ago and have used ever since.
ExcelIsFun I think I have an explanation for that. That blank cell serves as a kind of a 'place holder' where values from the selected list are placed. So each value from the list is placed in that 'empty cell', the formula runs making that 'empty cell' its reference, the output is known, and then the next item on the list occupies the 'empty cell' and the formula is re-evaluated and so on.
Vinay Kumar Chaganti , Really? Where did you learn that? Did you read it somewhere? I would love to know your reference for that cool bit of knowledge!
workbook link is down right now. Hope it comes back soon.
It has some video of how to solve this case PowerQuery or PowerPivot.
Hey Sir, kindly can you give me a solution for the problems which I face while duplicating tabs in my excel sheet then it asks that there is data already there and sometimes it takes main 2 or more minutes to click OK on dialog box which appears. Kindly, tell me if there is another shortcut.
? Do you have some macro VBA in Excel tutorials with file exercises as well
It will be delight to learn from your special and unique method material please.
Gilad Bark I do not have VBA code like that. Sorry.
+ExcelIsFun
Sorry for being not clear , I meant to tutorials of VBA for Excel in general with explanations , exercises and answers, not specifically about this Excel Trick.
Thank you!
No, I am not good with VBA so I don't make videos about this topic. Sorry.
+ExcelIsFun
Thanks anyway, you are great tutor!
I came back to this video, because I have exact situation. But, is there a simpler method to achieve this with Office 365 dynamic array?
The Adavanced Filter method is probaly still the fastest. However, now you can use FILTER with the MATCH and ISNA (Not in List) or ISNUMBER (Is in List).
I have the name Uzoma Joseph Mike in cell M16 and Mike Uzom in cell G24. They names refer to one person but cell G24 omitted one name and misspelled one other and reordered it. How can I know which name/word was misspelled and which omitted assuming I have a huge array? Please help me.
Sir why Should not substract ROW(38) at a time ?
how can it work on google sheet? google sheet not found this value. so help........
Hi, thanks for sharing this video. The final file (Download File: excelisfun.net/) is not availble after clicking on the link. Where to download it? Cheers!
I have the name Uzoma Joseph Mike in cell M16 and Mike Uzom in cell G24. They names refer to one person but cell G24 omitted one name and misspelled one other and reordered it. How can I know which one was misspelled and which omitted assuming I have a huge array? Please help me.
I have the name Uzoma Joseph Mike in cell M16 and Mike Uzom in cell G24. They names refer to one person but cell G24 omitted one name and misspelled one other and reordered it. How can I know which word/name was misspelled and which omitted assuming I have a huge array? Please help me.
How can i find missing values in a Multiples duplicates values in esy way?
COUNTBLANK
I need help I'm in a proficent level in excel and want to get at an advance level before i graduate I'm in accounting student Any good courses?
*an
Jr Arteaga Try the my Highline Excel Class playlist:
ua-cam.com/play/PLrRPvpgDmw0nh4BBxqjcsnbB4ME4JjzfJ.html
+ExcelIsFun Thank you so much ! Great stuff by the way !
whats the excel file called. Cant seem to find it
+Solomon Ocquaye 00:03 minute mark i say what file name. Click on minute hyperlink: 00:03 and check it out!
+ExcelIsFun , after clicking on the link below the video, Ctrl + F, then type file name.
i use pivot tables to compare
janquin11 So many cool ways to do things in Excel! :)
Please help asap.....
B. G
Method 3 is more complicated
Why don't you just put them in one column and delete duplicate data?? That way you will have one single list which is latest version.
how to extract from list 1 to list 2 ?☺
Ahmed Ismail Same but with the lookup_value looking in List 1 and lookup_array looking at List 2.
ExcelIsFun can u pls make an example ?
The e-mail example in this video shows exactly that.
ExcelIsFun Thank u Mr.Mike.
And another thing i need u to introduce is Power BI iam started to use it and its so amazing hope to see ur lectures about it soon :)
I have a few playlist of videos of Power Pivot and Power Query:
ua-cam.com/play/PLrRPvpgDmw0nGCx21PRFbsJpUIH06LKs-.html
ua-cam.com/play/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK.html