Changing Sort Order of Spending Trends Tab

Google Sheets
1

I was wondering how I could change the sorting for the Spending Trends Tab, specifically the breakdown by category. I think it would be great if the Groups were sorted largest $$ first and within the groups the categories with the most income/expense sorted in descending order. This would allow you to quickly zero in on all of the largest groups & categories that are moving the needle.

To me that would be much more efficient than the current alphabetical sort order.

I see in the hidden tabs some of the raw data is sorted that way, but I I’m not overly familiar with the LET function used in B11 that consolidates that data

1 Like
2

Hey @jeremycoenen ,

(I’m assuming you’re using Google Sheets.) The way the Spending Trends sheet constructs the subtotaled breakdown by Category makes it hard to sort, particularly since you want to sort on amounts but preserve the grouping and subtotaling by Group. Ultimately, it’s the formula in AL2 that determines the order in which the breakdown appears.

To sort Groups by decreasing amounts and then Categories by decreasing amounts, try replacing the formula in AL2 with the following:

={AD2:AG2;
  IFERROR(
    QUERY(
     {AC3:AG,
      MAP(AE3:AE,LAMBDA(GROUP_,MATCH(GROUP_,
        LET(TYPE_ARRAY,QUERY(AC3:AG,"SELECT Col3, SUM(Col5) WHERE Col1=false AND Col3 is not null GROUP BY Col3 LABEL Col3 '', SUM(Col5) ''",0),
          INDEX(SORT({INDEX(TYPE_ARRAY,,1),ARRAYFORMULA(ABS(INDEX(TYPE_ARRAY,,2)))},2,FALSE),,1)),0))),
      ARRAYFORMULA(ABS(AG3:AG))
     },
     "SELECT Col2, Col3, Col4, Col5 
      WHERE Col1=false AND (Col2="&ifs(Z2="Income & Expenses","'Income' OR Col2='Expense'",Z2="Income Only","'Income'",Z2="Expenses Only","'Expense'")&") 
      ORDER BY Col2 DESC, Col6, Col7 DESC LABEL Col2 '', Col3 '', Col4 '', Col5 ''", 
     0),
    {IFERROR(1/0),IFERROR(1/0),IFERROR(1/0),IFERROR(1/0)})
}
3 Likes
3

@rhowell is a spreadsheet wizard - noted :spiral_notepad:

2 Likes
4

thank you - that worked perfectly!

5

I was hoping to find a way to do this and happily found this thread. However, sometimes people might want to go back to the way it used to be, so I thought I’d post what my old version of the spreadsheet had in AL2.

={AD2:AG2;QUERY({AC3:AG},"SELECT Col2, Col3, Col4, Col5 WHERE Col1=false AND (Col2="&ifs(Z2="Income & Expenses","'Income' OR Col2='Expense'",Z2="Income Only","'Income'",Z2="Expenses Only","'Expense'")&") ORDER BY Col2 DESC, Col3, Col4 LABEL Col2 '', Col3 '', Col4 '', Col5 ''", 0)}
3 Likes
6

Does anyone have this for Excel? Thanks.