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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies