Forum Discussion
PIVOTBY Table with custom sorting
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.
I don't think it can be done with a setting in the PIVOTBY function but similar results could be achieved by assigning a serial number to each category and pivoting by that instead of the text category. For example
= PIVOTBY(product, month, quantity, AVERAGE,,0,,0)could become
= LET(
serial, DATEVALUE(1&month),
PIVOTBY(product, serial, quantity, AVERAGE,,0,,0)
)I would consider the second crosstab to be an improvement. If you do not have an obvious way of sorting, simply list the categories in order and use XMATCH to return an index.
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,
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.
Thanks, Peter. Appreciated!
Thanks for sharing your workbook so I can go thru it step by step.