I have been playing around with the question that @craigexcel had and where @Fluff and @JamesCanale had their valuable input, I just cannot crack this nut.
When trying to reproduce in an example, it returns only one line.
The formula I am using is:
=LET(
z, exampledata,
a, FILTER(z, INDEX(z, , 6) = "Meeting"),
b, SORT(SORT(a, 4, ), 7, ),
c, TAKE(DROP(b, , 7), , 1),
d, VSTACK(DROP(c, 1), 0),
e, c <> d,
result, FILTER(b, e),
result
)
Any suggestions as to how to adjust the formula to return the desired results? I appreciate your time at having a look at this.
- I'm using Office 365
- I have a sheet with headers and its data below. The data is named exampledata (headers in row 2; data in A3:L12
- In another sheet, I want a formula that filters on "Meeting" in column F (Type) so it returns only rows with "Meeting" and only shows the most recent date as per column G (Last)
When trying to reproduce in an example, it returns only one line.
The formula I am using is:
=LET(
z, exampledata,
a, FILTER(z, INDEX(z, , 6) = "Meeting"),
b, SORT(SORT(a, 4, ), 7, ),
c, TAKE(DROP(b, , 7), , 1),
d, VSTACK(DROP(c, 1), 0),
e, c <> d,
result, FILTER(b, e),
result
)
Any suggestions as to how to adjust the formula to return the desired results? I appreciate your time at having a look at this.
Example data | |||||||||||
# | Partner | Class | Company | Product | Type | Last | Prio | X | Next | Due | Action/notes |
1 | Partner 1 | Other | Company A | Other | Meeting | 31/01/2023 | 1 | TRUE | 02/02/2023 | x | Notes 1 |
4 | Partner 1 | Other | Company A | Other | Meeting | 22/03/2023 | 1 | TRUE | 24/03/2023 | Notes 2 | |
34 | Partner 2 | Other | Company D | Other | Meeting | 09/07/2023 | 1 | TRUE | 09/07/2023 | x | Notes 3 |
33 | Partner 3 | Other | Company E | Other | Action | 09/07/2023 | 1 | TRUE | 09/07/2023 | Notes 4 | |
35 | Partner 2 | Other | Company F | Other | Meeting | 11/07/2023 | 1 | TRUE | 13/07/2023 | Notes 5 | |
42 | Partner 2 | Other | Company D | Other | Meeting | 23/07/2023 | 1 | TRUE | 25/07/2023 | x | Notes 6 |
46 | Partner 3 | Other | Company G | Other | Meeting | 04/08/2023 | 1 | TRUE | 04/08/2023 | Notes 7 | |
47 | Partner 1 | Other | Company A | Other | Action | 04/08/2023 | 1 | TRUE | 04/08/2023 | Notes 8 | |
48 | Partner 1 | Other | Company B | Other | Action | 06/08/2023 | 1 | TRUE | 06/08/2023 | x | Notes 9 |
206 | Partner 1 | Other | Company C | Other | Meeting | 01/12/2023 | 1 | TRUE | 06/08/2023 | Notes 10 | |
Expected result | |||||||||||
# | Partner | Class | Company | Product | Type | Last | Prio | X | Next | Due | Action/notes |
4 | Partner 1 | Other | Company A | Other | Meeting | 22/03/2023 | 1 | TRUE | 24/03/2023 | Notes 2 | |
35 | Partner 2 | Other | Company F | Other | Meeting | 11/07/2023 | 1 | TRUE | 13/07/2023 | Notes 5 | |
42 | Partner 2 | Other | Company D | Other | Meeting | 23/07/2023 | 1 | TRUE | 25/07/2023 | x | Notes 6 |
46 | Partner 3 | Other | Company G | Other | Meeting | 04/08/2023 | 1 | TRUE | 04/08/2023 | Notes 7 | |
206 | Partner 1 | Other | Company C | Other | Meeting | 01/12/2023 | 1 | TRUE | 06/08/2023 | Notes 10 | |
Current result | |||||||||||
# | Partner | Class | Company | Product | Type | Last | Prio | X | Next | Due | Action/notes |
206 | Partner 1 | Other | Company C | Other | Meeting | 01/12/2023 | 1 | TRUE | 06/08/2023 | Notes 10 |