Filtering the sheet to the latest year

[skyrabbit]

Hi there! I am new to this forum and so please excuse me if this comes across a bit newbish. I'm working on a spreadsheet (see the attachment) that has routenames, start milepoints, endmilepoints, year and some condition data attached to it. What I want to do is this as follows-

For a cell in A2 (routename), find all the years that is attached to it (year ranges can be from 2007 TO 2013), filter out I2 coumn (Year) to see only the most recent year, and see all the other data in other columns attached to this year.

For eg, say if I select the first route, A2 is 016SC00600EB, year associated with that route is 2008 and 2013. I only want data in columns associated with 2013 to be displayed.

And I want this to be done to all the routes in Column A. Since there are more than a 100 routes, what's the best way to approach this.

 

[NARAYANK991]

Hi ,

See your file.

There are filters applied to columns T and V.

Narayan

 

[skyrabbit]

Damn, I keep forgetting about array formulas. This is awesome, man. Thanks a lot for figuring this out.

 

[skyrabbit]

Hi Narayan, I've stumbled upon one more issue and hoping you'll be able to crack this one.

I've attached a sheet as an example.
So here's the thing - Attached file has 2 sheets - Master and Reference. What I want is as follows -

First, look for 'route name' in master and match it with the one in reference table, if it matches, look for 'Start MP' and 'End MP' and if that matches, I want to return the 'surface type desc' in reference table to 'surface type' in master sheet.

I realize that the Start and End MP in Master can be between the ones in the reference table,

so even if the Start MP's match, i want to return the 'surface type descp'

Let me know if you can think of something. Thanks

 

[NARAYANK991]

Hi ,

I am getting an error message when opening the file ; can you please re-upload ?

If you can , check by downloading the file from your link.

Narayan

 

[skyrabbit]

Try downloading now.

 

[NARAYANK991]

Hi ,

I am not sure about your requirement ; see if this is OK.

Narayan