Out With the Old and In With the New

“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.

  1. Open both workbooks. Here they’re arranged vertically, so both lists are visible.

    OrdersDup01

  2. 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.
  3. To start the formula, in cell D2, type:   =COUNTIF(
  4. 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
  5. 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
  6. To complete the formula, type a closing bracket, then press Enter. Here’s the completed formula.
    =COUNTIF([Orders_Week01.xlsx]Week01!$A:$A,A2)
  7. 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. 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.

  1. Click in the Dups column heading, and press Ctrl+A, to select the entire range.
  2. On the Ribbon’s Data tab, click the A-Z button, to sort the list in ascending order.
    RibbonSort
  3. The new items (zeros) will sort to the top of the list, with the old items (ones) at the bottom of the list.
  4. Select all the rows with old items, right-click on a row button in the selected rows, and click Delete.
    OrderDup05
  5. Finally, to clean up the sheet, delete the Dups column.
  6. 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.)

Counting in Excel

There are more Excel counting tips on the Contextures website.

____________________________

You may also like...

7 Responses

  1. AlexJ says:

    Speaking of “out with the old …”, great new look for the site!

  2. NancyK says:

    I love that I actually understand your site. Unlike Dick’s where everything might as well be in Greek.

  3. Bob R says:

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

  4. 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.

  5. Roger Govier says:

    Hi Deb

    Good to know that “Mom” comes to the rescue – again!!!

  6. Contextures Blog » Conditional Formatting Is Better Than Your Faulty Memory says:

    […] 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 Contextures Blog » Conditional Formatting Is Better Than Your Faulty Memory Cancel reply

Your email address will not be published. Required fields are marked *