| 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?