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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies