Hey so I synced my tiller after like 3 weeks and it brought in a bunch of transactions, but some transactions are duplicated with only slight variations in their description. Anyone have experience with this? Thanks.
Is it happening across multiple accounts, or just one problem account?
I’ve been doing a lot of de-duplicating because I manually loaded data going back through 2020, and figured overlapping data would be easy to remove duplicates with Excel’s de-duplicator.
This does not address the Tiller assigned transactions IDS that are in hidden columns. Haven’t had that problem.
Here are 2 problems I had to solve:
Similar but not exact descriptions, same dates.
Exact Descriptions but the dates were shifted by 1 day because the financial institution somehow shifted the dates.
KEEP BACKUPS AS YOU WORK THROUGH DUPLICATES! LOTS OF FREQENT BACKUPS!
Note that I have the columns for my visibility reversed, so it’s a little confusing. Column Q “Count Similar” is dependent on column R “Desc Act Amt”
On the Transactions tab:
To clean up and look for more duplicates, add 2 new columns on the far right.
I call mine, “Count Similar” and “Desc Act Amt” The names are not important, just so you understand what they mean.
This looks for duplicates that are less than 2 days apart days apart. (1/1 through 1/2 will be counted 2, 1/1 through 1/3 will be counted as 1 - they are not dupes, too far apart)
In the 1st record of “Count Similar” put =COUNTIFS(R:R, R2, A:A, “>=” & A2 -1, A:A, “<=” & A2 + 1)
Where column R is the “Desc Act Amt”
Where column A is Tillers Date column.
Note you can change the ‘-1’ and ‘+1’ for the number of days spread between dates, like 3 or 4 days.
This is the result, for these 2 records that are 1 or more day apart. It counted 2 records with the same description, more 1 day apart.
|2| Driver and VehicleCredit Card 900-1.3
|2| Driver and VehicleCredit Card 900-1.3
The “Desc Act Amt” column R joins fields that make the transaction identical so the only variable would be the Date
=LEFT(TRIM(CLEAN([@Description]&[@Account])),15)&[@Amount]
Results for The Home Depot #1000 #1000 & Credit Card 900 & Amount of -72.8
The Home Depot -72.8
1 Like
Addendum: there has to be a better way of cleaning the “Description” column with more intelligent use of Excel’s string functions but I didn’t need that. Just a lot of filtering, putzing, pulling data out just to test it, etc. Textbefore(), Textafter(), TextBetween(), Left(), Right(), etc.