PIVOTBY Table with custom sorting

[tana]

Hi, does anyone know if there is way to custom sort the row of table made by PIVOTBY? Thanks,

 

[hagia_sofia]

Hello, generally speaking you could e.g. wrap PIVOTBY into SORTBY where by_array argument would refer to a range or an array containing the desired custom order or use a helper column with custom sorting and select it as part of row_fields argument.

 

[tana]

Hello, generally speaking you could e.g. wrap PIVOTBY into SORTBY where by_array argument would refer to a range or an array containing the desired custom order or use a helper column with custom sorting and select it as part of row_fields argument.

I have the following table

I would like to sort the rows as the following sorted order except Total row

Is it possible? Thanks,

 

[hagia_sofia]

Many thanks for posting the example. I presume that you have source data somewhere from which PIVOTBY calculates the outcome. Instead of using the row_fields range as you have it now (your current row_fields range), try to replace it with the following: REPT(UNICHAR(8203),XLOOKUP(your current row_fields range,your current custom order list,SEQUENCE(ROWS(your current custom order list),,ROWS(your current custom order list),-1)))&your current row_fields range. By "your current custom order list" I mean the list in the table 2 above. I.e. something like:

REPT(UNICHAR(8203),XLOOKUP(A2:A1000,E1:E10,SEQUENCE(ROWS(E1:E10),,ROWS(E1:E10),-1)))&A2:A1000

 

[tana]

Many thanks for posting the example. I presume that you have source data somewhere from which PIVOTBY calculates the outcome. Instead of using the row_fields range as you have it now (your current row_fields range), try to replace it with the following: REPT(UNICHAR(8203),XLOOKUP(your current row_fields range,your current custom order list,SEQUENCE(ROWS(your current custom order list),,ROWS(your current custom order list),-1)))&your current row_fields range. By "your current custom order list" I mean the list in the table 2 above. I.e. something like:

REPT(UNICHAR(8203),XLOOKUP(A2:A1000,E1:E10,SEQUENCE(ROWS(E1:E10),,ROWS(E1:E10),-1)))&A2:A1000

Thanks for your help, Sofia.

I think I found the solution for now. Since I am not that familiar with REPT & UNICHAR functions. I end up using LET statement & XMATCH to retrieve the index order.

Appreciate for all your help!!!