Compare lists in Excel: 6 ways to find differences

We often need to compare lists in Excel, and find out which items are in one but not the other. In this video, I show 6 methods for doing that including conditional formatting, data validation, Power Query and 3 formula methods =UNIQUE(VSTACK()), =COUNTIFS and =VLOOKUP.
You can download the example files here: www.xlconsulting-asia.com/you...
For more on approximate matching, see this video: • Fuzzy matching in Powe...
00:00 Introduction
00:31 Highlight them
01:26 Big red dircle
02:25 COUNTIFS
03:37 VLOOKUP
03:59 UNIQUE and VSTACK
05:58 Power Query anti join

[@ziggle314]

Nice job. I really liked your use of multiple conditional formats to home in on the unique and duplicates. Thanks.

[@learnspreadsheets]

Glad you like that! Yeah it eliminates the option of having non matches that are hidden!

[@jerrydellasala7643]

TIP: Use the formula "=TEXTBEFORE(CELL("filename"),"[")" when defining a "Folderpath" name to import into Power Query. Note, that will have a trailing backslash which the hard coded path had in the file "Advanced filter in Excel filter by list.xlsx", but you can also get that with "=LET(Txt,TEXTBEFORE(CELL("filename"),"["), Ln, LEN(Txt), LEFT(Txt,Ln-1))".
As long as I'm here, I couldn't find the file for this video either.

[@learnspreadsheets]

Thanks for the tip’

[@user-zj2no8kn7c]

Saying clicking on this and that is not very explanatory, say the names of what did intend to do/click on .

[@learnspreadsheets]

Thanks for the comment. I’m sorry you feel that way, I just rewatched the video to check, & i feel the only places where I say this/that, I’m either referring to a range of cells which I’m clearly showing on screen or a button that I click which I am clearly showing too, that doesn’t really have a clear name (like a dropdown to choose the validation type or the range selector) but I’ll pay more attention to that in future