Jeff’s Quick Tips: Comparing two Excel lists

In this edition of Jeff’s Quick Tips, I’ll tell you how Excel’s COUNTIF function made me the hero for a client who needed to analyze a lot of data in a hurry.

The Dilemma
I got an email with a workbook attached and one sentence:  “I need to know any duplicates and keep those.”

Translation:  The client had a list of tens of thousands of marketing account codes in one column, and another list of only a few thousand similar-looking marketing codes in another column. What the client really wanted to know was:  “How many of the codes in the second column appear in (are duplicated in) the first column?”

The Solution
This solution I used was a formula that looks at each value in column B and says, “How many times does that value appear in column A?” Ladies and gentlemen, I give you that formula:  =COUNTIF(A:A,B2). The following screen shots show how it works on some dummy data.

Which records in the first list are also in the second list?

The user wanted to know which records in Control List appeared in the Big List.

CountIf-2

The solution used was the formula =COUNTIF(A:A,B2), which says “Count how many times what’s in B2 appears anywhere in Column A.”

CountIf-3

The COUNTIF funtion tells how many times each entry in column B appears in column A.

CountIf-4

Use Data | Filter to un-check the records in the second list that weren’t found (the ones where COUNTIF returned 0).

The Final Report

I’m not sure why the client needed to compare these lists or what he would do with the “duplicates,” but I was 100% sure he had the right result.

CountIf-5

Here’s what the final output looked like: The COUNTIF function told us not only which records were “duplicated” in the big list, it told us how many times the value appeared.

 

ToolTalk BackDid you find this tip useful? We’d love to hear your feedback in the Comments section below.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply