CODEWITHSUNDEEP
excelvba
D. Todor
D. Todor

Reputation: 157

VBA Excel Use row with most recent date

I am currently working on a VBA script to find the row with the most recent date value for each unique value of another cell.

For example:

enter image description here

I would like to have all the data corresponding with unique values of A (City) and only the most recent date.

So it should look a little bit like:

enter image description here

Like mentioned it should sort all data in all columns, not only A and B.

Does anybody have an idea how this could be done the best way? I was trying to use formulas but I wasn't succesful so far.

Thanks in advance.

Upvotes: 0

Views: 511

Answers (1)

JNevill
JNevill

Reputation: 50019

You can do this with an array formula. Assuming your data is in columns A:B and you have NYC in Cell D1 then you can do: 

=MAX(IF(D1=A1:A100, B1:B100, 0))

That's an array formula so you'll have to enter it in by holding Ctrl+Shift+Enter

Once you get that list, you can do a VLookup or similar to pull over the rest of the values.

Upvotes: 1

Related Questions