Account Filter Not Working

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

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

Anil

Randy,

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

Anil Parikh

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

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.

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.

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

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

Is that column present?

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

Is the column deleted or renamed?

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.

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.

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

ID column is still there.

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.

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

A few things don’t look right…

  1. 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.
  2. 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.

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

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

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.

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?

© 2021 Tiller LLC

Accounts

Account

Costco Anywhere Visa

Account

H

Hide

Q

Date

Description

Category

Amount

Account

Account

‘Accounts’!$H$2:$H

6/2/2021

The Home Depot #0660 Encinitas CA

Rep&Maint-Indirect

-$1,118.96

Costco Anywhere Visa

Hide

‘Accounts’!$Q$2:$Q

6/2/2021

Amazon.com*2x7xb8tm1 Loading...

Misc-Toiletries

-$7.52

Costco Anywhere Visa

Transactions

6/2/2021

Amzn Mktp Us*2x5ac7eh1 Amzn.com/billwa

Clothing

-$7.53

Costco Anywhere Visa

Date

C

5/31/2021

Amazon.com*2xx2393d1 Loading...

Misc-Toiletries

-$7.13

Costco Anywhere Visa

Description

D

Category

E

Amount

F

Account

H

Date

‘Transactions’!$C$2:$C

Description

‘Transactions’!$D$2:$D

Category

‘Transactions’!$E$2:$E

Amount

‘Transactions’!$F$2:$F

Account

‘Transactions’!$H$2:$H

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

Anil