Using Hidden Tags

Google Sheets
1

Hi!!

I’ve spent so much time learning and fine tuning Tiller reports. Loving the control and exercise to improve gSheet skills. I came across another puzzle. This one has me stumped.

I want to hide tags from reports. This is fine when I only have a single tag at most per transaction. I modified the Actuals query in the Monthly Budget to include a matches criteria. Simplified below

query(Transactions!A:Z,“SELECT Col(category),SUM(Col($)) WHERE Col(category) IS NOT NULL AND Col(date) >= date '”&TEXT(date,“yyyy-mm-dd”)&“’ AND NOT Col(tag) matches '”&AB52&“’ GROUP BY Col(category) LABEL SUM(Col($)) ‘Actuals’”)

AB52 is a cell with all Hidden tags separated by pipe (e.g., “Trip|Hide|Invalid”)

The problem is this doesn’t work if I have more than 1 tag for the transaction. I separate them out by “,” but the query only checks for the whole string.

I can’t for the life of me figure out how to check an array of texts to see if it includes a word in another array of texts within a query (or a sumifs if needed). If that was all I needed to do, it would be simple to just arrayformula over a match, but because it needs to be embedded in a query or sumifs, I can’t use an arrayformula.

I actually ran into the same problem trying to create a table of actuals by month with a number of criteria including the comparison of a text array to another text array.

Any help would be appreciated.

2

I have written a helper column in Transactions for this (equation below), but I’d love to make it part of an arrayformula so I don’t have to copy paste the formula for all new transactions.

=if(isblank(B2),iferror(1/0),sum(ifna(arrayformula(match(iferror(split(L2,“,”),0),iferror(split(‘Monthly Budget’!$AB$52,“|”),0),0)),0))>0)

3

Seems like your helper column could use something like the formula on this page =COUNTA(IFERROR(FILTER(Array_A;MATCH(Array_A;Array_B;0)))) in an ArrayFormula but I don’t have the time right now to try and make it work.

4

Appreciate the assistance. Unfortunately I can’t use an arrayformula with a filter, so while this is an elegant solution to a single row with an array embedded in a cell, I wouldn’t be able to apply the rule across a range of rows.

The one nice thing about my helper rule is that, so far, it only uses formulas that are compatible with an arrayformula. I just can’t seem to get it to apply for “L2:L” – It may be impossible to have a nested arrayfromula like this though.

5

What if you use the CONTAINS keyword in your QUERY() instead of MATCHES? Unfortunately, I can’t find any documentation from Google on this but there seem to be some references here.