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.
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?”
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.
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.
ToolTalk BackDid you find this tip useful? We’d love to hear your feedback in the Comments section below.