«

»

Keep the last update of your data

Too many data for the same contact

Let's start from this file where we have different informations about our clients. Sometimes, we have many different data for the same customer.

We want to keep only the newest information for each customer based on the column Update.

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

The most important part of the solution is to

  • Find a column with an ID (here the email)
  • Use hybrid references

Hybrid references is something we don't use often in Excel. A hybrid ref means that only one reference of the range is locked. The other reference is free.

=COUNTIFS($I$2:I2,I2)

If we look each part of the function, we have

  • The first reference of the range is the first cell of email (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, this function is copied for the rest of the cells.

The result is the following

Now, if we look at the formulas, we have this


When we copy down, the range of selection extended.

So if we compare the picture with the result and the picture with the formula we can noticed for the email CarolGCoen@einrot.com

  • The first formula is just focus on the row 2. And the number of times the email is found it's obviously 1.
  • Now, for the row 7, the range of selection is $I$2:I7 so the number of times the email is in this range it's 2 times.
  • And so on for the rest of range

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 one than more time.

Step 4: Filter your data

The job is nearly done 😉

We just have to

  • Filter (Data>Filter) on the column with the formula we have created
  • And you unchecked the value 1

Like this, we display the rows to be deleted

Step 5: Delete the rows

  • Select all the visible rows
  • Right-Click>Delete row or Shortcut Ctrl + -

Step 6: Remove the filter

 

 

 

 

Conclusion

Now, our list is up-to-date with the last information for our client

Permanent link to this article: https://www.excel-exercise.com/keep-last-update-data/


Leave a Reply

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