SOLVED

Index and Sort formula help

Brass Contributor

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?

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

@kittenmeants 

How about

 

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

You're amazing.

Thank you!!

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

 

 

 

1 best response

Accepted Solutions
best response confirmed by kittenmeants (Brass Contributor)
Solution

@kittenmeants 

How about

 

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

View solution in original post