Profit & Loss Report 'Uncategorized' & 'Category not Found' - Ghosts?

Google Sheets
,
1

I have a question about the Profit & Loss Report workflow from Tiller Community Solutions. The report generates a section along the too as UNCATEGORIZED and “Category not Found”

I can’t find what the error is. What I know to be true is

  1. Everything is categorized
  2. All categories used in Transactions sheet are on the Categories sheet
  3. If I manually SUM transactions in January, it shows the same NET INCOME.
  4. The rest of the P&L Report is correct. All income and expenses are accounted for. If I deleted this top section called UNCATEGORIZED, the report would be perfect.

SO, where is it coming up with this? Which categories? What are the numbers it is using to generate a total amount uncategorized?

Jan 2023
UNCATEGORIZED
Category Not Found ($1,731)
Total UNCATEGORIZED ($1,731)
2

Hi @zalzal - Welcome! I tested this on my end and found out that a purely uncategorized transaction will show up in the report as Group “UNCATEGORIZED” and category “Uncategorized” (on the 2nd line where you have “Category Not Found”). However when I changed the transaction from blank/uncategorized to Category “test” which is a category that does not exist in my Categories sheet, then it changed to “Category Not Found”, the same way that yours shows in the provided screenshot. So it leads me to think that you may want to double-check that the Category is indeed in the Category sheet exactly the same way as it is in the Transactions sheet. Maybe there’s just a slight typo difference?

A couple quick ways to identify this would be:

  1. If you don’t already have it, set up a data validation on your Category column in the Transactions sheet.
    Highlight the whole Category column and go to menu Data–>Data Validation and set it up as follows (note that your Category column letter is likely different than mine).

CleanShot 2024-09-25 at 14.04.46

The one that doesn’t exist should have a little red triangle in the top right corner indicating an invalid input that doesn’t exist on the Categories sheet.

Another option is to drop the following query formula into a new blank sheet and it should return any rows found in the Transactions sheet that have a Category assignment that doesn’t exist or doesn’t match in the Categories sheet. If none exist, it should return “No Result”. The row number of the transactions will be in the first column of the query result. Note that you might need to format the date column in the output to make it more readable.

=LET(catcol_num, MATCH("Category",Transactions!1:1,0), catcol_let, REGEXEXTRACT(ADDRESS(1,catcol_num,4),"[A-Z]+"),
IFNA(QUERY({ARRAYFORMULA(ROW(Transactions!A1:A)),ARRAYFORMULA(IFNA(XLOOKUP(INDIRECT("Transactions!"&catcol_let&"1:"&catcol_let),Categories!A1:A,Categories!A1:A),"NF")),Transactions!A1:Z},"select * where Col2 = 'NF' and Col"&catcol_num+2&" <> '' label Col1 'Row #', Col2 'CatMatch'",1),"No Result"))

Hopefully those ideas might help get to the bottom of the issue.

1 Like
3

@zalzal did the recommendation in this thread help?