Hi there! I just started using the Weekly Trends page from the Community Solutions and I’m wondering how I can add a Group column so that the rows can be sorted by Group (instead of alphabetically by Category Name). I can’t quite figure out how this sheet is structured so my initial attempts to modify it all ended in failure. Anyone have any suggestions on how to approach this?
If you’re talking about having the group populate based on what category you select for the transaction then you can add an additional column in your transactions that contains the following array formula to accomplish this.
=ARRAYFORMULA(IFERROR(VLOOKUP(D:D,Categories!A:B,2,FALSE),IFERROR(1/0)))
This assumes that your category column is in column D and your group column on the categories page is in column B if those two things are true then this formula will work without modification
As long as row 1 doesnt get moved you should be able to sort as requested
Yup, I can do that in the Transaction sheet! But I when I use the same approach in the Weekly Trends sheet and then sort, all the data disappears. The array formula in the Weekly Trends sheet looks like this:
=iferror(QUERY(J4:N, “SELECT L, SUM(M) WHERE L <> ‘’ AND N <> ‘Hide’ GROUP BY L PIVOT J”, 1))
But I don’t know if the QUERY formula will just default to alphabetical order? And thus sorting will completely fail to work every time?
The QUERY is referencing a data table of total spend by category by week, in case that helps.
My apologies, i missed that it was on that sheet instead of just populating them to the transactions sheet, let me mess with that this evening and see, if someone else isnt able to help before then.
This query stuff is a bit beyond my scope, but I was able to figure out that If you just want to change the category view to group view, then in K4 change the regex extract lookups from Category to Group after having the other Array formula in your transactions sheet.
You’ll also want to change K1:K2 to Group as the sparklines reference that
The new formula in K4 will look like
=QUERY(INDIRECT(“Transactions!A:AE”), “SELECT " & REGEXEXTRACT(ADDRESS(1,MATCH(“Date”, Transactions!A1:AF1, 0)), “[A-Z]+”) & “,” & REGEXEXTRACT(ADDRESS(1,MATCH(“Group”, Transactions!A1:AF1, 0)), “[A-Z]+”) & “, SUM(” & REGEXEXTRACT(ADDRESS(1,MATCH(“Amount”, Transactions!A1:AF1, 0)), “[A-Z]+”) & “) WHERE " & REGEXEXTRACT(ADDRESS(1,MATCH(“Date”, Transactions!A1:AF1, 0)), “[A-Z]+”) & " >= DATE “”” & TEXT(NOW()-WEEKDAY(NOW(),3) - 28, “YYYY-MM-DD”) & “”” AND " & REGEXEXTRACT(ADDRESS(1,MATCH(“Date”, Transactions!A1:AF1, 0)), “[A-Z]+”) & " <= DATE “”" & TEXT(NOW() - WEEKDAY(NOW(), 3) + 6, “YYYY-MM-DD”) & “”" GROUP BY " & REGEXEXTRACT(ADDRESS(1,MATCH(“Date”, Transactions!A1:AF1, 0)), “[A-Z]+”) & “,” & REGEXEXTRACT(ADDRESS(1,MATCH(“Group”, Transactions!A1:AF1, 0)), “[A-Z]+”) & " LABEL SUM(" & REGEXEXTRACT(ADDRESS(1,MATCH(“Amount”, Transactions!A1:AF1, 0)), “[A-Z]+”) & “) ‘Total’”, 1)
Another option may be to make the lookup reference Cell K1 so that when changed to Category or Group everything will updated accordingly that part would be MATCH(K1, in both spots in the query formula
If you are looking to have both Group and Category and sort by group, I’m not sure I’ll be able to assist in that endeavor.
To sort in a different order you would need to modify the query that you referenced in C5
1 Like
I figured it out, with a bit of help from Chat GPT : )
- Added a Group column to the data table in columns J:N (just adding a new column O and did a Vlookup on Category to find Group)
- Modified the Query to call both the Category and Group columns (L and O), then included the Group column (O) in the Group By instruction
So the new Query call is:
=QUERY(J4:O, "SELECT L, O, SUM(M) WHERE L <> ' ' AND N <> 'Hide' GROUP BY O, L PIVOT J", 1)
That way I get both Group and Category in the created table, and Group is the driver for how the rows are ordered.
And then, of course, update the reference for the sparkline graphs for the correct columns! : )
1 Like
Well done, @eliande! It’s so satisfying when you pull of something challenging like this. Toast yourself.
2 Likes
Thanks so much for jumping in to help, Ben!! 
1 Like
, 