Find Excel List Duplicates With COUNTIF

Find List Duplicates With COUNTIF in Excel

“Help!” said the familiar voice, when I picked up the phone at 10 PM.
“I have a list of orders in an Excel sheet. I want to compare it with the list from last week, and delete all the orders that were in the old list.”

It was my daughter, still at the office, trying to get a pile of work done before the looming deadline. I helped her with a COUNTIF formula, and she was able to leave for home a short time later. Phew!

Find Duplicates With COUNTIF

The first step is to check each OrderID in the new list, to see if it’s also in the old list.

We’ll use a COUNTIF formula to calculate how many times each OrderID is found in the old list. If the count is zero, we know it’s a new order.

Prepare the Worksheets

  • Open both workbooks. Here they’re arranged vertically, so both lists are visible.
two workbooks arranged vertically
two workbooks arranged vertically
  • In the new workbook, add a column heading, Dups, in cell D1 in this example. This step isn’t required, but keeps things tidier when you try to sort later.

Add COUNTIF Formula

  • To start the formula, in cell D2, type: =COUNTIF(
  • Next, we’ll tell Excel where to look for the OrderID. In the old list, click on the column heading for column A, where the Order IDs are listed. That adds a reference with the workbook name, sheet name and column.
  • =COUNTIF([Orders_Week01.xlsx]Week01!$A:$A

OrderDup02

  • Finally, we’ll tell Excel what we want to look for. Type a comma, then in the new list, click on the OrderID in cell A2.

OrderDup03

  • To complete the formula, type a closing bracket, then press Enter. Here’s the completed formula.
  • =COUNTIF([Orders_Week01.xlsx]Week01!$A:$A,A2)
  • Copy the formula down to the last row of data in the new list. There are 1s in some rows and 0s in other rows.

Check Formula Results

We can see that the first three numbers in the new list are also in the old list, and they have been correctly counted as 1.

The next three numbers aren’t in the old list, so their count is zero.

OrderDup04

Delete the Duplicates

Now that the new orders are identified with a zero, we can delete the old orders.

  • Click in the Dups column heading, and press Ctrl+A, to select the entire range.
  • On the Ribbon’s Data tab, click the A-Z button, to sort the list in ascending order.

RibbonSort

  • The new items (zeros) will sort to the top of the list, with the old items (ones) at the bottom of the list.
  • Select all the rows with old items, right-click on a row button in the selected rows, and click Delete.

OrderDup05

  • Finally, to clean up the sheet, delete the Dups column.
  • Save a copy of the revised file, send it off to your vendor, and go home! (Well that’s how our scenario ended – you might have to stay at work for a few more hours.)

Video: Count Specific Items with COUNTIF

See how to use Excel COUNTIF function to count cells in a list that contain specific words or part of a word. For example, how many orders were for a Pen? How many orders for any kind of pen, such as “Gel Pen”, “Pen” or even a “Pencil”?

Video Timeline:

  • 00:00 Introduction
  • 00:22 Example 1 – COUNTIF Exact Match
  • 00:46 Enter Criteria in Formula
  • 01:20 Example 2 – Partial Match
  • 02:00 Criteria for “Contains”

Counting in Excel

There are more Excel counting tips on the Contextures website.

More COUNTIF Links

For more examples of using the Excel COUNTIF function, see these blog posts:

Problems Counting Excel Data

COUNTIF Challenge

Check Winning Numbers with COUNTIF

Use COUNTIFS for Multiple Criteria

Count Numbers in a Range

Quickly Change COUNTIF Criteria

Count Cells Greater Than Set Amount

____________________________

7 thoughts on “Find Excel List Duplicates With COUNTIF”

  1. Debra – I also like the new look! I would have used vlookup, then filtered on the cells with #NA and deleted those rows.

  2. Nancy, glad you like it! Dick’s blog is geared to people who want to perform brain surgery, or achieve world domination, using Excel. 😉 I’m aiming for readers who want to get their work done a bit faster, or solve an annoying Excel problem, so they can move on to something else.

    Thanks Bob R, and you’re right, a VLookup would have worked too. In this example we’d have to keep the #NA rows (new records), and delete the rest.

  3. […] could add a new column, with a COUNTIF formula to count the number of times each file appears in the Update list. Instead, I’ll use conditional formatting to colour the rows […]

Leave a Reply to Bob R Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.