Dec 23 2018 03:18 PM - edited Dec 23 2018 03:20 PM
Just wanted to show an example how to use new SORT and FILTER Excel functions available in Office 365 to enumerate rows. It's an equivalent to SQL window function ROW_NUMBER() OVER (PARTITION BY a, b ORDER BY c)
In the following example all flights are stored in a table, Flight No uniquely identifies individual flights. We want to enumerate flights for each combination Route + Fleet No. in ascending or descending order.
In ascending order:
=MATCH([@[Flight No.]],FILTER([Flight No.],([Route]=[@Route])*([Fleet No.]=[@[Fleet No.]]),""),0)
In descending order:
=MATCH([@[Flight No.]],SORT(FILTER([Flight No.],([Route]=[@Route])*([Fleet No.]=[@[Fleet No.]]),""),1,-1),0)