Ad Hoc solution to support sub-categories

As a Quicken user, I miss the ability to assign sub-categories to transactions in Tiller. I thought I would share my ad hoc solution. I have created my Tiller category as a ‘compound’ string to include a category and sub-category separated by a colon. Examples of my Tiller categories are as follows:

|Category|Group|Type|
|Donations: Deductible|Discretionary|Expense|
|Donations: Non-Deductible|Discretionary|Expense|
|Entertainment: Gaming|Discretionary|Expense|
|Entertainment: Miscellaneous|Discretionary|Expense|
|Entertainment: Newspaper/Magazine|Discretionary|Expense|
|Entertainment: Streaming|Discretionary|Expense|
|Expense: Bank Fees|Non-Discretionary|Expense|
|Expense: Business|Non-Discretionary|Expense|
|Expense: Cash|Discretionary|Expense|
|Expense: Miscellaneous|Discretionary|Expense|
|Expense: Professional Fees|Non-Discretionary|Expense|
|Food: Groceries|Living|Expense|
|Food: Restaurants|Discretionary|Expense|
|Gifts: Gifts In|Income|Income|
|Gifts: Gifts Out|Discretionary|Expense|
|Home: Improvements|Discretionary|Expense|
|Home: HOA|Bill|Expense|
|Home: Household Items|Living|Expense|
|Home: Maintenance|Living|Expense|
|Home: Mortgage|Bill|Expense|

The spreadsheet table does not copy well into this template. For example, from above, you can see that I have two categories for Food: 1) Food: Restaurants and 2) Food: Groceries. Yes, this does add to the number of categories that I have in Tiller but is equal to the number of sub-categories I have in Quicken. And then using the Auto-Cat feature for the reoccurring transactions, it really takes no time to assign these extended categories. Then I have added columns in my Transaction spreadsheet using formulas to extract the category text on each side of the colon to create individual columns of the category and sub-category. This then allows me to sort, filter and total transactions to meet my reporting needs.

Clever, @jcw0207. Many users use tags, but I see the value in your approach.

We really appreciate that you took the time to share this with others!

1 Like

I did the same thing with compound categories separated by a colon.
I view this as a NEED TO HAVE more than a NICE TO HAVE; when importing historical data from other PF software. Tags don’t really cut it… especially if the legacy PF software uses them for other uses than categorization. Good work JCW!

I do the same thing for subs except I use a middle dot, for example, “Food · Groceries”. A middle dot can be generated on a macOS device by typing Shift+Cmd+9.

What are the formulas you use to extract the text for category/sub-category? I was going to use “Text to Columns” if formulas didnt work.

1 Like

Brendan,
I have Col O named ‘Category2’ and Col N named 'Sub-Category.

The formula in Col 0, Row 2 (and is then added to all rows) is as follows:

=LEFT([@Category],(FIND(“:”,[@Category],1)-1))

This formula is using the FIND formula embedded within the LEFT formula. The FIND formula finds and returns the position of the colon. The LEFT formula extracts a defined number of characters from the left of a text string. Translated to English, the above formula is starting at the left of text string in the current row of the Category, then finding the position of the colon starting at position 1 and subtracting 1 from that value and then extracting the result number of characters from the string.

The formula in Col N, Row 2 (and is then added to all rows) is as follows:

=MID([@Category],(FIND(“:”,[@Category],1)+1),50)

This formula is using the FIND formula embedded within the MID formula. The FIND formula finds and returns the position of the colon. The MID formula extracts a defined number of characters starting from a defined position within the string. Translated to English, the above formula is starting at calculated MID position of the text string in the current row of the Category, then finding the position of the colon starting at position 1 and adding 1 to that value and then extracting the next 50 characters from the string. The value of ‘50’ in the MID formula is defining the maximum number of characters to extract. If any sub-category is more than 50 characters, then increase this value accordingly.

The Transaction tab will look like this (some columns hidden):

Jamie

2 Likes