Forum Discussion

kittenmeants's avatar
kittenmeants
Brass Contributor
Sep 07, 2023
Solved

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 ta...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Sep 07, 2023

    kittenmeants 

    How about

     

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

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 07, 2023

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