Forum Discussion

kittenmeants's avatar
kittenmeants
Brass Contributor
Sep 07, 2023

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 TypeYear - 2023Year - 2024
A100 
B12 
C50 
D60 
E2 

 

Top 10 Transaction Type 2023Count of Transactions 2023Top 10 Transaction Type 2024Count of Transactions 2024
A100  
D60  
C50  
B12  
E2  

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

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

     

     

     

Resources