"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.
- Open both workbooks. Here they're arranged vertically, so both lists are visible.
- 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.
- 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.
- 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.
- To complete the formula, type a closing bracket, then press Enter. Here's the completed formula.
- 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.
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.
- 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.
- 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.)
Counting in Excel
There are more Excel counting tips on the Contextures website.