Index and Sort formula help

[kittenmeants]

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?

[#2 · Accepted solution]

@kittenmeants 

How about

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

[#3]

You're amazing.

Thank you!!

[#4]

@kittenmeants 

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