fbpx
Search
Close this search box.

CP021: How to quickly compare 2 lists in Excel

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

14 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

  13. Geoff Holland says:

    I have two lists of email addresses and I'm wanting to compare both lists and identify and duplicates. The only catch is the comparison is only the @companyname.com

    Is this possible

Leave a Reply