• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

Remove rows from worksheet based on chronology

I have a large worksheet with multiple rows per 'id' that need to be filtered so that I only get the row with the most recent visit date per 'id'  

See attached  very abbreviated example.  I would need to get back the rows with most recent visitdate for each 'id' so I would want rows 3 and 7.

Is there an easy way to do this with VBA or Filters?
testexcelsheet.xls
0
snailcat
Asked:
snailcat
  • 4
  • 3
1 Solution
 
Saqib Husain, SyedEngineerCommented:
I cell D2 enter

=A2=A3

and copy down and then filter by false.
0
 
snailcatAuthor Commented:
ssagibh,

This is giving me "FALSE" for the last row for each ID but I need to have the most recent date which is the second row for ID 1.

Any way to do this without
0
 
snailcatAuthor Commented:
continuing--
Any way to do this without sorting by date first?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Saqib Husain, SyedEngineerCommented:
Sorry, try this array formula

=ROW()=MATCH(MAX(IF((A2=$A$2:$A$7),$B$2:$B$7)),IF((A2=$A$2:$A$7),$B$2:$B$7),0)+1

and then filter true
0
 
snailcatAuthor Commented:
Tried this but only giving TRUE for row 7

See attached
testexcelsheet2.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
This is an array formula.

Select D2
Press F2
Press Ctrl-alt-enter simultaneously
You will see braces {} around the entered formula. These are array formulas.

You can now copy the cells down.
0
 
snailcatAuthor Commented:
Thanks for getting me in the correct direction
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now