Home

[Example] How to enumerate rows in a table using multiple criteria. [Office 365]

drumsta
Occasional Visitor

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)

 

 

1.jpg