CP021: How to quickly compare 2 lists in Excel

Posted on October 2nd, 2014 in Chandoo.org Podcast Sessions , Excel Howtos - 13 comments

In the 21st session of Chandoo.org podcast, lets compare lists. Quickly

How to quickly compare 2 lists in Excel - CP021 - Become awesome in Excel, data analytics, charting, dashboards & VBA  - Chandoo.org Podcast.

What is in this session?

Comparing things is a favorite pastime for analysts all over the world. Sadly, it is also an area where we waste hours. So in this episode, I share my top secret comparison techniques to save you time.

Note: This is a short format podcast. That means you spend less time listening to it, while becoming more awesome.

In this podcast, you will learn,

  • Why I sound like I am on a secret mission at a mafia hideout.
  • 5 ways to compare 2 lists
    • Manual method
    • Conditional Formatting
    • Row Differences
    • LOOKUP formulas
    • COUNTIF formulas
  • Bonus tip: Removing duplicates
  • Conclusions

Listen to this session

 

Links & Resources mentioned in this session:

Please go thru below links for example workbooks on each type of comparison.

  1. Comparing 2 lists in Excel using conditional formatting
  2. Comparing 2 lists using row differences
  3. Comparing 2 lists using various formula techniques
  4. Special case: Comparing 2 lists with conditions
  5. Special case: Comparing 2 lists of first & last names
  6. Special case: Compare 2 files side by side
  7. Removing duplicates in Excel

Transcript of this session:

Download this podcast transcript [PDF].

How do you compare? Tell us quickly

So how do you compare? My favorite methods are conditional formatting & COUNTIFS formulas. What about you? Please share your tips using comments.

13 Responses to “CP021: How to quickly compare 2 lists in Excel”

  1. Trouttrap2 says:

    I did not know about using row differences from the Goto Special. That is truly awesome. Thank you for sharing. I tend to compare two rows of dates and this is perfect, and way better than eye-strain.

  2. Sal says:

    To compare two lists showing differences I use
    =ISNA(MATCH(A1,H:H,0)) in conditional format using formula
    Column A been the list with the extra data and H been the column to compare

  3. Kerel says:

    If I have two sets of data, I paste them underneath each other, give a criteria in a variable (e.g.: list 1, list 2). And then pull a pivot table over the whole dataset. Then you can compare both datasets on each criteria in your source data.

  4. Scot says:

    I have a tip to add: Using the Sort dialog box you can change the "Sort On" from sort by Values (which is the default) to sort by Cell Color, sort by Font Color or sort by Cell Icon.

    So here is the awesome application: If you have many many rows and you need to check for duplicate entries in a column, of course you can apply conditional formatting. You still have the problem of scrolling through many many rows to look for the highlight color. But if you sort the data by the cell color or font color after applying conditional formatting, you can get all the duplicates to sort to the top!

  5. Hi Chandoo

    Please advise when we do use =+ formula in Excel???
    How a Formula can Start with =+ and what is the use of this formula.

    thanks

  6. Doug Glancy says:

    Good topic! I recently also posted about data comparison on the Daily Dose of Excel at http://dailydoseofexcel.com/archives/2014/09/22/data-comparison-tricks/.

    Here's an array formula from that post for comparing two identically-sized lists. If they match cell-for-cell it returns TRUE:

    =AND(A2:C10=E2:G10)

  7. vijay says:

    Hi Chandoo

    Grt listening to your sessions. My favourite method of doing comparison of two data done with Vlookup in excel.

    I do use the advanced filter to remove duplicates from the lists.

  8. vijay says:

    Hi Chandoo
    Grt listening to your sessions. My favourite method of doing comparison of two data done with Vlookup in excel.

    I do use the advanced filter to remove duplicates from the lists.

  9. srikanth says:

    Hi Chandoo Sir,

    This is srikanth from Andhra Pradesh,
    I designed some vba projects in excels,
    I 100% Google student,
    Then I want a best job in vba, I seeing
    For best package like 8 lakh per annum, I am ready to share my projects to you,
    Pl suggest me if u have any jobs.

    My contact no: 9948199009
    My mail I'd : sri09948199009@gmail.com

    Thanking you,

  10. Rashmi says:

    Hi Chandoo,

    Thanks a lot for the wonderful blog. I got many useful stuffs from this blog. This blog is too good. One thing if possible try to add some tips on Microsoft Project (MSP), S-Curve etc for project management professional. It would be help full for us.
    Here i required some help, i prepared an excel sheet which contain my project status like many activities( which are directly related to my project). I want to put automatic tick when one activities got completed and at same time the status must got changed. I tried through conditional formatting but i didn't get the required result.
    Can please guide me in this matter.

  11. samuel says:

    that is a good masterpiece

  12. Afrizal Ramadhan says:

    cool, many of my question hanging over me solve. thanks anyway

Leave a Reply