I need help understanding some code in the Tags Report

Google Sheets
1

I really like the Tags Report as written in the Community Solutions. A couple of years ago, I wanted to see something similar by Payee (Description). This was before the Transaction Tracker report was available.

I copied the Tags Report, named it Payee Report, and changed field A9 to reference Q6 (Description) instead of Q9 (Tags). Changed the words Tags to Payee as appropriate and I had an easy new report that gave me the detail I was looking for.

This was working great for me until the end of May. I am not getting the list of Total Payees in Cols ABC, but columns D-L are working as expected (but locked in to the last Payee/Description previously selected). I have looked at version histories and the transactions I downloaded on 5/30/24 appear to be when the issue started. I don’t think it’s the data itself because if I use a smaller date range, the sheet populates as expected (with those 5/30 transactions included). I tried adding blank lines at the bottom of the sheet to give it more room, but that doesn’t make any difference.

I think it has something to do with field O10 (the letter O) which is labeled Query Range with =“'”&N$4&“'!$”&CHAR(64+MIN(O5:O9))&“2:”&CHAR(64+MAX(O5:O9)) that translates to ‘Transactions’!$B2:G (which is what I want). I’m not sure what the MIN/MAX is trying to do.

I think the problem is with the quantity of data that it is trying to organize for Cols ABC. I was trying to look at 2024 transactions (there are about 2600 of those) and my Transactions sheet is about 9700 transactions from 2021-2024 (I have 2019-2020 in a different sheet).

Does anyone have any ideas for me for an easy fix? I am not a Google Sheets expert and this may be some Google limitation as well.

Thanks in advance for any suggestions on what to try next.

2

Cols ABC are completely empty, row 9 to the end?

I would be more suspicious of the Cell A9, B9, C9 formulas and they don’t reference Query Range, which you said is correct anyhow.

Here’s Cell A9, with the Q9 to Q6 change:

=IFERROR(SORT(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",UNIQUE(TRIM(FILTER(INDIRECT(Q6),INDIRECT(Q5)>=O18,INDIRECT(Q5)<=O19)))),",") ) ) ),IFERROR(1/0))

Try removing the outer IFERROR(,IFERROR(1/0)) to get any error hints, like this:

=SORT(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",UNIQUE(TRIM(FILTER(INDIRECT(Q6),INDIRECT(Q5)>=O18,INDIRECT(Q5)<=O19)))),",") ) ) )

Note that JOIN and SPLIT are using commas as a delimiter, so any Payee with a comma would get split up into multiple payees.

I believe JOIN and/or SPLIT may have character limits, so maybe that’s what you’re running into.

3 Likes
3

Hi @martha.rudkin - that’s an interesting use case. I agree with @Mark.S that I think it has to do with the A9 and related formulas. Specifically I think it has to do with a limitation on the JOIN function which creates a single text string before it splits the string back out again. I understand it may have a 50,000 character limitation so you’re probably hitting that after your latest data load. It’s possible that you don’t need the JOIN and SPLIT for your case. I suspect when it was designed for Tags, it was because they were designed for multiple entry and separated by commas. So it was natural to split them. For your description/payee field, you probably don’t need it. So you could just remove that part and see if that resolves the problem. Try something like this in A9:

=IFERROR(SORT(UNIQUE(TRIM(FILTER(INDIRECT(Q6),INDIRECT(Q5)>=O18,INDIRECT(Q5)<=O19)))),IFERROR(1/0))

I don’t have a long enough data set to properly test it but hopefully that helps.

2 Likes
4

Thank you so much @Mark.S and @KyleT for your helpful suggestions.

I tried the simpler A9 that @KyleT provided and everything is working - even when I set my date range to be the entire sheet.

Again, thank you!!

2 Likes