Sorting data with the latest year

[skyrabbit6]

Hello All! I have a list of routes with milepoints and data attached to it that was collected in years varying from 2007 up untill 2013. Is there any way I can parse/filter the sheet to see the most recent data for a particular route. I have attached a sample of the spreadsheet. Any help is appreciated.

[mcmahobt]

Filters are already applied to the worksheet. If I'm understanding your question correctly, just filter by both Route Name and then Year...but if I'm understanding your question incorrectly please elaborate.

[skyrabbit6]

I see. There are multiple routes and each route has data attached it to based on the milepoints. The milepoints are from start to end but are broken in small sections. Say if a route starts from 0 to 5, it'll be broken down to 0-2,2-3,3-5. My question is since i have mutiple routes, is there any way i could filter the entire sheet to just display the most recent year for that route instead of me going to an individual route and selecting the most recent year and getting the data.

[FDibbins]

Hi, welcome to the forum

1st, your "dates" are not really dates, they are text that looks like a date. This will not present a problem with sorting, but I suggest you convert them to real values (explained later, below)

To sort your data, click the small drop-down arrow at the top of column I, and select Sort A-Z
Then click the Drop-down arrow on the Route (or any other column you want to filter on_ and uncheck all tat you dont want to see

To convert those dates to values, highlight that entire range, scroll back to the top, click the yellow diamond that appears to the left of I2 and select Convert to Number

[FDibbins]

Can you show a small sample of what you want (done manually with copy/paste if needed)

[skyrabbit6]

Sure! I have attached a sample of what I want the spreadsheet to look like. I've selected the first two routes 016SC00600EB and 016SC00601EB. As you've noticed there are mutiple years attached to it in the Year column. But, for 016SC00600EB, the most recent year is 2013 and for 016SC00601EB, the most recent year is 2011. I so want the year column to be filtered in such a way that for a cell in A2, I want the most recent year attached to it.

[FDibbins]

OK did you try my suggestion in post #3? It looks like that should work for you?