Keep the last updated row
Let's start from a file storing customer information.
Some customers have multiple records in the file, as a new record was created each time they changed their address or phone number.
How do you keep only the last updated row?
Remove duplicates not applicable
With a such file, you can't use the tool Remove Duplicate. Well, you can use it but the tool will remove nothing.
Why? It's because each row has something different. Like the phone number for instance.
Step 1: Use the formula COUNTIFS
Firstly, we are going to count how many times we have the same ID. An ID is a unique value that identifies a person, a product, .... The function to count rows is the function COUNTIFS.
The ID in our example is the email address.
But to solve our problem, there is a trick ⚠💡🧐 We must use hybrid references
Hybrid reference is something we don't often use in Excel. A hybrid ref means that only one reference of the range is locked. The other reference is relative.
If we look at each part of the function, we have
- The first reference of the range is the cell containing the email field on the first row of the excel sheet (reference locked)
- The second reference is the same cell but ref unlocked
- Finally, the criteria of the function is the first email of the list
Step 2: Copy the formula
Then copy this function down the rest of the cells in the column.
The result is the following:
Now, if we look at the formulas, we find this
When we copy down, the selection range is extended.
So if we compare the picture with the result and the picture with the formula we notice that for the email CarolGCoen@einrot.com:
- The first formula just focuses on row 2. And the number of times the email is found is obviously 1.
- Now, once we have copied down to row 7, the selection range is $I$2:I7, so the number of times the email is found in this range is 2 times.
- And so on for the rest of ranges.
Step 3: Sorting the data
Now we have to sort our document on 2 keys:
- The email (the criteria of the COUNTIFS function)
- The Update from the newest to the oldest
Open the menu Data>Sort
- Select the first key (the email)
- Add a new level
- Select the column with the formula
- Change the order (newest to oldest)
As you can see, it's easy now to visualize when a client is present more than once in the Excel sheet.
Step 4: Filter your data
The job is nearly done 😉
We have to use the filter to select all the values greater than 1.
We just have to:
- Filter (Data>Filter) on the column with the formula we have created
- And uncheck the value "1"
Step 5: Delete the rows
Here is the trick. We will delete all the visible rows.
- Select all the visible rows
- Right-Click>Delete row or Shortcut Ctrl + -
Step 6: Remove the filter
The job is done, so we can remove the filter of the columns.
Now, our list is up-to-date with the latest customer information.