Forum Discussion
kittenmeants
Sep 07, 2023Brass Contributor
Index and Sort formula help
Hello!
I need help modifying this formula to accommodate the new year, 2024.
So this is what the table currently looks like(small version), and I am pulling this information to a separate table to display our top 10. I am using this formula:
=INDEX(SORT(B16:C130,2,-1,FALSE)SEQUENCE(10),{1,2})
Transaction Type | Year - 2023 | Year - 2024 |
A | 100 | |
B | 12 | |
C | 50 | |
D | 60 | |
E | 2 |
|
How can I modify the formula to account for 2024 only?
- kittenmeantsBrass ContributorYou're amazing.
Thank you!!
- PeterBartholomew1Silver Contributor
This would do the two years in a straightforward manner but if you wanted an array of years that would be a little more challenging. The year 2024 only requires
= LET( ordered, SORT(table,2,-1), TAKE(ordered, n) )
but to extend the formula to include 2024 would require
= LET( table2023, HSTACK(TransactionType, Year2023), table2024, HSTACK(TransactionType, Year2024), ordered2023, SORT(table2023,2,-1), ordered2024, SORT(table2024,2,-1), TAKE(HSTACK(ordered2023, ordered2024), n) )