I am running a query in Google Sheets to analyze a bank account statement's transaction text (column D) against predefined categories (found in range
$T14:$BG14 for this particular row).
My problem is that every once in a while the transaction text is blank, and within my range of predefined texts there will also be blanks for certain categories, which produces a false match. As you can see from the screenshot below, each category can be set to match against any number of transaction texts.
Is there a way to make this line
D matches '" & JOIN("|";$T14:$BG14) &"'
only match if the value is not blank (or null or whatever Google Sheets will identify an empty cell as)?
Here is the entiry query, and some sample data.
QUERY( 'Kontoutdrag länsförsäkringar'!$A$2:$F$999176; "SELECT sum(F) WHERE B >= date'" & TEXT(DATEVALUE(C$4);"yyyy-mm-dd") & "' AND B <= date '"& TEXT(DATEVALUE(C$5);"yyyy-mm-dd") &"' AND D matches '" & JOIN("|";$T14:$BG14) &"' label sum(F)'' "; )
Cells U17 and V17 in this screenshot will for example create a match for every line on my bank account statement that lacks a transaction text.