Account Filter Not Working

anilparikh1 · May 28, 2021, 12:07am

Randy

I reread your email. On Accounts Sheet when I unhide columns to the right, I do see list of Accounts to the right:

However, my Sheet References columns look different from your:

Does this help?

Anil

anilparikh1 · May 28, 2021, 12:14am

Another point. I have not added any columns to Accounts sheet. So I only see default columns A to D before unhiding.

Anil

anilparikh1 · June 1, 2021, 9:04pm

Randy,

I have responded to your questions in Tiller Community last Thursday. Could you please help with the issue.

Anil Parikh

randy · June 1, 2021, 9:41pm

My screenshot is from the Account Filter template.
Can you unhide and show an image of that one, @anilparikh1?

anilparikh1 · June 1, 2021, 10:31pm

Looks as though you missed two screenshots after unhiding my Account Filter. Please scroll up and you will see them. If that is not what you are looking for, please advise.

randy · June 1, 2021, 10:33pm

I’m confused. Aren’t the screenshots above from your Accounts sheet?
I thought the issue you are having is with the Account Filter template.

anilparikh1 · June 1, 2021, 10:51pm

My apology, Randy. I unhid Accounts Sheet, not Accounts Filter Sheet.Here is the screenshot.

Sheet References
Accounts
Account	H
Hide	Q
Account	‘Accounts’!$H$2:$H
Hide	‘Accounts’!$Q$2:$Q
Transactions
Date	C
Description	D
Category	E
Amount	F
Account
Date	‘Transactions’!$C$2:$C
Description	‘Transactions’!$D$2:$D
Category	‘Transactions’!$E$2:$E
Amount	‘Transactions’!$F$2:$F
Account	‘Transactions’!$$2:$
List of Accounts
Alliant PKRT Checking
Alliant PKRT Savings
Ally Mak Trust CD
Ally Mak Trust Savings
Ally PKRT CD
Ally PKRT Savings
Anil Signature Visa
Chase PKRT Checking
Chase PKRT Savings
Costco Anywhere Visa
ETrade PKRT Checking
ETrade PKRT Living Trust
HealthEquity MAK HSA
Joint UA Visa
Lloyds Bank Intl Joint - (30C7)
NatWest Joint
Santander Intl Joint
Schwab AP Contributory IRA
Schwab AP Individual 401(k)
Schwab MAK Contributory IRA
Schwab MAK Living Trust
Schwab PKRT Living Trust
SDCCU AP PKRT Checking
SDCCU AP PKRT Savings
SDCCU Joint Trust Checking
SDCCU Joint Trust Savings
SDCCU MAK Trust Checking
SDCCU MAK Trust Savings
Synchrony MAK HY Savings
Synchrony PKRT Savings

Anil

randy · June 1, 2021, 10:53pm

It looks like it can’t find the Account column in the Transactions sheet.

Is that column present?

anilparikh1 · June 2, 2021, 12:25am

You are correct. There was Accounts Column before Account# in Transactions sheet. This appears to have disappeared. How do I get it back?

randy · June 2, 2021, 2:27am

Is the column deleted or renamed?

anilparikh1 · June 2, 2021, 2:52am

It was renamed to Receipt. I renamed back to Account. New transactions pull the Account name but not old transactions. I can use “IF” function to pull in Account name if Google sheets work the same as Excel. But I have almost 30 Accounts so it will be very time consuming. Is there a better way.

randy · June 2, 2021, 3:00am

Tiller Feeds use reserved column headers to fill data. If you change the name of a feeds column, the feeds process will not know where to insert data and will just skip insertion of that field for new rows. Changing the name back will allow the field to be included for new data, but the records that were already inserted (without the Account field for example) will have already been marked as inserted and will not be reconciled.

The data is all in the Tiller back end if you create a new Foundation Template and allow it to fill (since the time your account was created). If you really want to backfill the data, you could create a new Foundation Template, link the same accounts, fill all your data into the Transactions sheet, copy your new Transactions sheet into your old Tiller spreadsheet, and then use a formula to VLOOKUP() the account names matching on the Account Id field. Once you have filled the Account column, you could copy and paste all the data as values. This is very doable but it will take a little time and expertise.

randy · June 2, 2021, 5:39am

Crazy thought… is there a chance your Transactions sheet has an Account ID column? Perhaps it is hidden?

anilparikh1 · June 2, 2021, 5:03pm

ID column is still there.

randy · June 2, 2021, 8:32pm

So you restored the Account column to its original name? And there has been enough time that the most recent rows have the Account field/cell populated by Tiller Money Feeds?

You could do this:

Add a new empty temporary column to the right of the partially populated Account column
Add a formula like this in the new column to the right of empty Account cells where L4 is a reference to the Account ID column in the row with the missing account name, $L$2:$L is the Account ID column contents and $G$2:$G is the Account name column contents:
=iferror(vlookup(L4,{$L$2:$L,$G$2:$G},2,false))
Copy the account names from the formula VLOOKUP() in the new column
Paste those account names into the Account column as values
Delete the new/temporary column

Hopes this helps you fill the missing data, @anilparikh1.

anilparikh1 · June 2, 2021, 10:00pm

I think, I followed your instructions correctly but when I hit ENTER, I get a blank cell. See screenshot below. I think the error must be due to number of columns before “false” in the formula. In my case 2 cannot work. I counted columns from left but did not work. Formula is correct as you van see rows H and J are selected:

Anil

randy · June 2, 2021, 10:11pm

A few things don’t look right…

It doesn’t look like you have filled Account names for any but the xxxxxx90 account. For this to work, you need to have all of the account names you want to find populated ABOVE the lookup formula.
I suggested using the Account ID column not the Account # column. Account # may work but there is more risk of 1-to-many matches giving incorrect results.

anilparikh1 · June 2, 2021, 11:29pm

You asked me to use Account ID column instead of Account# column. But I do not see it Transactions Sheet. I can find all my Accounts in Account Filter Sheet if I unhide columns at the right.

Anil

randy · June 4, 2021, 6:21am

I suggested the Account ID column because you said it was present.

You need to make sure all of the accounts have filled in their account names before this approach will work. Then, map the $L$2:$L / Account ID from my formula to Account # instead.

anilparikh1 · June 7, 2021, 3:12am

My apology for misreading Account ID for Account number. Unfortunately it will take a long time for Accounts to have their Account names filled in as some of my 30 Accounts do not have even monthly activities.

So I created a Table of Account Names and Account# and then followed your formula using Vlookup for Account number instead of Account ID. This has worked for all Accounts except for Costco Anywhere Visa credit card. See screenshot below for this card. Transactions sheet shows 884 transactions for Costco. But Account Filter only shows 4 transactions. I have restored Accounts, Balances and Account Filter in that order. But still only 4 transactions for Costco Visa. Any suggestions?

Accounts

Account

Costco Anywhere Visa

Account

H

Hide

Q

Date

Description