excelexcel-formula

Only sort filtered list within a value from another column (similar to the partition by function in SQL)

0 A B C D E F G
1 date airline airplane Filter A-319
2 2022-09-15 Airline_A B-787
3 2021-03-27 Airline_A A-319
4 2023-12-26 Airline_A A-319 Expected Result:
5 2024-09-30 Airline_B B-787 2021-03-27 Airline_A A-319
6 2023-09-25 Airline_B A-319 2023-12-26 Airline_A A-319
7 2025-01-02 Airline_B A-319 2021-08-12 Airline_B A-319
8 2021-08-12 Airline_B A-319 2023-09-25 Airline_B A-319
9 2020-04-20 Airline_C B-787 2025-01-02 Airline_B A-319
10 2019-02-02 Airline_C A-319 2019-02-02 Airline_C A-319
11 2021-08-09 Airline_C A-319 2021-08-09 Airline_C A-319
12 2024-07-25 Airline_C A-319 2022-04-03 Airline_C A-319
13 2022-04-03 Airline_C A-319 2024-07-25 Airline_C A-319

In Range E5:G13 I want to filter the data from Column A:C based on the filter in Cell F1 and - as a first step - sort it by the Column B.
Therefore, I have implemented this formula in Cell E5:

=LET(
a;FILTER(A:C;(C:C=F1));
b;SORT(a;2;1);
b)

All this works fine.

Now - as a second step - I want to sort the filtered list ascending based on the date.
However, this sorting should only apply within the values from Column B.
In the example this means that the sorting of the airline should stay as it is and within the airline the date should be listed ascending. The expected results in Range E5:G13 display how it should look like.
In SQL this topic is solved with the PARTITION BY function.

=LET(
a;FILTER(A:C;(C:C=F1));
b;SORT(a;2;1);
c;SORT(b;1;1);
c)

How do I need to change the c part in the Excel formula to make it work?

Solution

  • Something along the lines of:

    enter image description here

    =LET(
     a, FILTER(A:C,C:C=F1),
     SORT(a,{2,1},{1,1}))

    Can exclude the sort_order by default it will be in ascending order:

    =LET(
     a, FILTER(A.:.C,C.:.C=F1),
     SORT(a,{2,1}))