SOLVED

# Index and Sort formula help

Copper 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

 Top 10 Transaction Type 2023 Count of Transactions 2023 Top 10 Transaction Type 2024 Count of Transactions 2024 A 100 D 60 C 50 B 12 E 2

How can I modify the formula to account for 2024 only?

3 Replies
best response confirmed by kittenmeants (Copper Contributor)
Solution

# Re: Index and Sort formula help

=INDEX(SORT(B16:D130,3,-1,FALSE)SEQUENCE(10),{1,3})

You're amazing.

Thank you!!

# Re: Index and Sort formula help

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)
)``````