Forum Discussion

tanacal's avatar
tanacal
Brass Contributor
Mar 02, 2025
Solved

PIVOTBY Table with custom sorting

Hi, does anyone know if there is way to custom sort the row of table made by PIVOTBY? Thanks,
excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Mar 02, 2025

    I don't think there are any shortcuts but once you are into the 365 environment with LET you can define variables to use in place of helper ranges.

    = LET(
    index,    XMATCH(Category, CategoryOrder),
    crosstab, PIVOTBY(index, Year, Amount, SUM),
    rowHdr,   IFERROR(INDEX(CategoryOrder, TAKE(crosstab,,1)),""),
    HSTACK(rowHdr, DROP(crosstab,,1))
  )

    Here index is used for to sort the row categories and, once done, look up the appropriate row header.

     

tanacal's avatar
tanacal
Brass Contributor
Mar 02, 2025

Thanks so much for your prompt response, Peter. 

I have the following table made by PIVOTBY.

I would love to have the rows arranged by this type of order.

I understood what you suggested earlier, which is adding one more column in the data indicating the sorting order, did I understand it right? 
Is there any short-cut rather than adding one more column in dataset? Thanks, 

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Mar 02, 2025

I don't think there are any shortcuts but once you are into the 365 environment with LET you can define variables to use in place of helper ranges.

= LET(
    index,    XMATCH(Category, CategoryOrder),
    crosstab, PIVOTBY(index, Year, Amount, SUM),
    rowHdr,   IFERROR(INDEX(CategoryOrder, TAKE(crosstab,,1)),""),
    HSTACK(rowHdr, DROP(crosstab,,1))
  )

Here index is used for to sort the row categories and, once done, look up the appropriate row header.

 

  • tanacal's avatar
    tanacal
    Brass Contributor
    Mar 02, 2025

    Thanks, Peter. Appreciated!

    Thanks for sharing your workbook so I can go thru it step by step. 

Resources