How to filter multiple entries based on latest date?

[benobee]

Hi there

I have a spreadsheet of about 2000+ rows. Each row contains a number of columns, but the 2 key columns are the following:

Customer ID
Date

For each customer ID, there will be multiple rows for that customer, each row being a record of information of why they had contacted us.

However, I would like to filter this spreadsheet based on the latest entry only for each customer.

Is this doable without a macro? (i'm not great with macros). If so, how? If not, then so be it, but could you help illustrate how the macro would look please?

Any help would be much appreciated.

Many thanks in advance!

Kind Regards

benobee

 

[AlphaFrog]

First sort on ID and Date, then filter on ID.

• Select All your Data
• Select from the menu: Data\Sort
  • Sort by ID then by Date Descending
  • This will group like IDs together with the most recent at the top of each ID group.
• Then select the ID column
• Select from the menu; Data\Filter\Advanced Filter
  • Filter the List in Place
  • Unique Records Only
  • This will show only the first record for each ID which is the most recent.

 

[benobee]

First sort on ID and Date, then filter on ID.

• Select All your Data
• Select from the menu: Data\Sort
  • Sort by ID then by Date Descending
  • This will group like IDs together with the most recent at the top of each ID group.
• Then select the ID column
• Select from the menu; Data\Filter\Advanced Filter
  • Filter the List in Place
  • Unique Records Only
  • This will show only the first record for each ID which is the most recent.

Fabulous, worked a treat. Many thanks!!

 

[AlphaFrog]

You're welcome.

 

[nzlsapkota]

What we have to do, when we need the same date data with different skunames?

 

[Akira_94]

First sort on ID and Date, then filter on ID.

• Select All your Data
• Select from the menu: Data\Sort
  • Sort by ID then by Date Descending
  • This will group like IDs together with the most recent at the top of each ID group.
• Then select the ID column
• Select from the menu; Data\Filter\Advanced Filter
  • Filter the List in Place
  • Unique Records Only
  • This will show only the first record for each ID which is the most recent.

Registered to say that this solution also worked for me.
I needed to identify the stock movimentation's "last date" in our ERP, from 162 different Product_IDs. I also needed the data contained throughout the row that the date was placed in, and this solution was perfect.

Thanks for the help.

 

[AlphaFrog]

You're welcome from 10-years-ago me. Glad to see it still works .