Finding Invalid Categories on Transactions Sheet

Overview

You probably have randomly noticed this little red triangle and corresponding error message on a Category within the Google Sheets Transactions sheet (or Data Validation Error triangle in Excel):
Invalid: Input must fall within specified range

Transactions sheet

This category is invalid, because it is not found on the Categories sheet. You may simply fix this occurrence by changing the Transaction sheet Category to match an existing Categories sheet Category, and hope to just move on with your day.

… meanwhile, in the back of your mind …
… how did that happen? … is the rest of my data okay? …
… what if there are more of them? …
… how do you find them all with thousands of transactions? …

Not to worry, a couple formulas make them easy to find.

Installation

The new formulas get added to the Spending Trends sheet that is already included by default with a new Tiller Foundation Template spreadsheet.

If your spreadsheet does not have the Spending Trends sheet, install it from the menu:
Extensions > Tiller Money Feeds > Templates > Spending Trends

What if I do not want to use/modify the Spending Trends sheet?

Setup

Google Sheets

Modify the Spending Trends sheet using these steps:

  1. Copy cell G8, Paste-special-Format-only into cell H8 (Format only)
  2. Copy/Paste-special-Values-only the following formula into cell H8 (Values only):
    =COUNTA(IFNA(FILTER(INDIRECT(AA20),ISNA(XMATCH(INDIRECT(AA20),INDIRECT(AA23))),INDIRECT(AA20)<>"",INDIRECT(AA18)>=AA10,INDIRECT(AA18)<=AA11)))
  3. Right-click cell H8 > Insert note, copy/paste the following text:
    Invalid Transaction Categories.
    See unique list in Cell L33
    You may need to open the column group above
    the column letters (a plus sign box), to see column L.
    
  4. If column L is not visible, click + sign above column J to expand the Group to make column L visible.
  5. Copy/Paste-special-Values-only the following text into cell L32 (Values only):
    Invalid Transaction Categories:
  6. Copy/Paste-special-Values-only the following formula into cell L33 (Values only):
    =SORT(UNIQUE(IFNA(FILTER(INDIRECT(AA20),ISNA(XMATCH(INDIRECT(AA20),INDIRECT(AA23))),INDIRECT(AA20)<>"",INDIRECT(AA18)>=AA10,INDIRECT(AA18)<=AA11))))
  7. Set Text wrapping to overflow for cells L32 and below, if not already set that way.
Details

The new formulas leverage/use the Spending Trends sheet helper columns that dynamically handle Transactions/Categories sheet column locations that are different from the Tiller Foundation Template defaults.

Cell Value
AA10 Start Date
AA11 End Date
AA18 Transactions Date
AA20 Transactions Category
AA23 Categories Category
Microsoft Excel

Modify the Spending Trends sheet using these steps:

  1. Copy cell G8, Paste-Formatting into cell H8 (Format only)
  2. Copy/Paste-Values the following formula into cell H8 (Values only):
    =LET(f,FILTER(Transactions[Category],(ISNA(XMATCH(Transactions[Category],Categories[Category]))*(Transactions[Date]>=AA11)*(Transactions[Date]<=AA12))),COUNT(IF(f<>"",1)))
  3. Right-click cell H8 > Insert note/comment, copy/paste the following text:
    Invalid Transaction Categories.
    See unique list starting in Cell L34
    You may need to open the column group above
    the column letters (a plus sign box), to see column L.
    
  4. If column L is not visible, click + sign above column J to expand the Group to make column L visible.
  5. Copy/Paste-Values the following text into cell L33 (Values only):
    Invalid Transaction Categories:
  6. Copy/Paste-Values the following formula into cell L34 (Values only):
    =SORT(UNIQUE(FILTER(Transactions[Category],(ISNA(XMATCH(Transactions[Category],Categories[Category])))*(Transactions[Category]<>"")*(Transactions[Date]>=AA11)*(Transactions[Date]<=AA12),"")))
  7. Disable Wrap Text for cells L33 and below, if not already set that way.
Details

The new formulas leverage/use the Spending Trends sheet helper columns and were tested using Excel for the web.

Cell Value
AA11 Start Date
AA12 End Date

Usage

The Spending Trends sheet can now be checked to monitor for Invalid Transaction Categories. The results are filtered by the Spending Trends selected time period.

For example, here is what three Invalid entries might look like now, where there are 3 total Invalid entries with unique values of Air Travel and Hotel (one of them occurs twice).
Cells G8, H8:
Spending Trends sheet

Cells L32 and below:
Spending Trends sheet

Now that you know how many Invalid Transaction Categories there are and what their names are, use the Transactions sheet Data Filter for the Transactions sheet Category column to select/correct the problematic entries. Transactions sheet Category needs to match a Categories sheet Category.

Permissions

Feel free to copy, use, and modify in your workflow.

Notes

The Spending Trends sheet already has a “Needs Categorizing” count for the number of categories that are blank within the Transactions sheet, so it makes sense to add a separate count for Invalid categories next to it, since these need proper categorizing as well.

The new formula results are filtered by the Spending Trends sheet Start/End Date, consistent with the original “Needs Categorizing” blank cell count.

Google Sheets is my primary Tiller use case and I used Excel for the web to test the Excel formulas with sample data.

FAQ

How did these Invalid entries happen?

They typically happen due to manual editing:

How do I fix them?

Here are some options, depending on how they occurred:

But I do see the category on my Categories sheet, why is it flagged invalid?

Here are some reasons:

  • The new Spending Trends formulas are filtered by date, so check the selected time period includes the invalid category transaction date.
  • Check that the category names exactly match, including number of spaces.
  • The Transactions sheet Category column data validation might be incorrect - troubleshoot the Category dropdown

I see an invalid category on my Transactions sheet, why does the reporting say zero?

The new Spending Trends formulas are filtered by date, so check the selected time period includes the invalid category transaction date.

How do I check my entire list of transactions for invalid categories?

Change the Spending Trends time period (cell C6) to Custom and then change the Start Date, End Date to include your entire transactions date range.

What if I do not want to use/modify the Spending Trends sheet?

Consider using your own new/existing sheet and enter these two formulas into two different cells:

Google Sheets
  • Invalid Transaction Category count
    =COUNTA(IFNA(FILTER(INDIRECT("Transactions!$D$2:$D"),ISNA(XMATCH(INDIRECT("Transactions!$D$2:$D"),INDIRECT("Categories!$A$2:$A"))),INDIRECT("Transactions!$D$2:$D")<>"")))
  • Invalid Transaction Category unique list (leave cells open below this cell, as it fills downward)
    =SORT(UNIQUE(IFNA(FILTER(INDIRECT("Transactions!$D$2:$D"),ISNA(XMATCH(INDIRECT("Transactions!$D$2:$D"),INDIRECT("Categories!$A$2:$A"))),INDIRECT("Transactions!$D$2:$D")<>""))))

Where Transactions D and Categories A are the Category columns of each sheet, respectively. Change these column letters in the formulas to match your spreadsheet, if they are different. Note the date filtering is removed, so these check all transactions.

Microsoft Excel
  • Invalid Transaction Category count
    =LET(f,FILTER(Transactions[Category],(ISNA(XMATCH(Transactions[Category],Categories[Category])))),COUNT(IF(f<>"",1)))
  • Invalid Transaction Category unique list (leave cells open below this cell, as it fills downward)
    =SORT(UNIQUE(FILTER(Transactions[Category],(ISNA(XMATCH(Transactions[Category],Categories[Category])))*(Transactions[Category]<>""),"")))

Note the date filtering is removed, so these check all transactions.

Is there another way to find them?

Visually inspect the Category Tracker “Type / Group” column B for empty/blank cells - that Category is likely invalid as there is not a Category Type and Group defined for it - probably because this Category does not exist in the Categories sheet. The Category Tracker is a popular solution, but it is not included by default with a new Tiller Foundation Template.

This is so cool @Mark.S !! Thanks so much for publishing this solution. I get this question often :slight_smile:

Thanks, I hope it helps, and I certainly linked/referenced quite a bit of your help topics for support documentation :slight_smile:

1 Like

This is great and well documented solution to a common data integrity problem, @Mark.S. Thanks for sharing this with such great instructions and for both platforms. I just upvoted your solution.

1 Like

Not sure what I’ve done incorrectly, but Cell H8 is showing as “0” even though I can see I have invalid categories on my Transaction sheet.

Is the invalid category within the Spending Trends date range?

FAQ > I see an invalid category on my Transactions sheet, why does the reporting say zero?

Thank you! Appreciate your quick response, that did the trick. I love this addition.

I made a small addition to this. If the cell is “0” then I use conditional formatting to make the text white. That way I’m not looking at a floating “0” indefinitely.

1 Like

Incognito :disguised_face: :+1:

1 Like