Need Help Creating a Monthly Credit Card Only Reconciliation Template

[alondc]

Hi there

So I have a unique situation here, and I haven’t been able to find a template that fits my needs here and can’t figure out if I should try to build my own or if I’m looking in all the wrong places.

Every month my wife and I dump all of our expenses (mostly credit card transactions but a few other ones here and there) into a google sheets spreadsheet. We then go through the sheet and assign each expense into 1 of 4 categories.

1. Shared
2. Groceries
3. A
4. E

Once all of the expenses have been categorized, I use a few sumif formulas to calculate and split totals in a few different ways (shared=50/50 split, groceries=65/35 split, A or E= 100/0 split. We then use these totals to calculate how much of the expenses each partner is responsible for.

I am trying to figure out how to create a template in tiller that produces similar results by performing the below actions once a month.

1. create a new tab for this month and update the date range (18th of the previous month - 17th of this month)
2. Import all expenses from 1 cc account
3. Import expenses from other select accounts that match a list of filters/criteria
4. Auto assign an owner to transactions with criteria that match specific rules (similar to autocat)

• If need I can create the formula manually but ideally, it would produce the total that each of us needs to contribute to our expenses at the end of the month.

I’m not sure if there is something similar I can use to build on modify or if I should try to create something from scratch, and I would really appreciate some guidance on my next steps.
Thanks in advance for your advice!

[jono]

Hi @alondc ,
I don’t think there are any templates that match exactly what you are doing. But using some Google Sheet formulas, you should be able to build everything you are looking for.

See this for how to add new automatic columns to your Transaction sheet based on just one formula at the top of the sheet:

Adding New Columns On The Transaction Tab Automatically

Hi all, I was wondering if someone knew the workaround add-in certain columns to the transactions tab automatically. Currently, I am manually adding columns O and R with a Vlookup that I have to drag up every time new data is added in. This tells me what type of category it is and then goes down to another sub-level that I then use for data manipulation later. I think I’m just going blank because I know that this can be done automatically but not sure how. Can anyone help? (I would embed an …

You should also look into the SUMIFS() formula in addition to SUMIF().

You might also use the FILTER() function to import the list of expenses that match your criteria.

Hopefully that will get you started.

Jon

[jpfieber]

Some of the categorizing may be able to be automated using AutoCat. If you always shop at the same grocery stores, you could add AutoCat rules for those transactions so they automatically get the “Groceries” category. The same may be true for many of your other expenses. Now that I have my AutoCat rules built out, I rarely have to categorize my transactions, they mostly are automatically categorized for me.