Finding duplicate Transaction ID's

Ask Anything
1

Somehow, I have duplicate transactions in my Tiller data file.

What is the best way to identify duplicate items based on the transaction ID’s?

Thanks for any help on this.

1 Like
2

The best way I’ve found to do this is to put a conditional formatting rule on transactions sheet that highlights rows when there are duplicates present. Then, I go through and delete one of the transactions until there are no rows highlighted. Doing this, if there are two duplicate TransactionIDs, the entire row will be highlighted for each entry. Then, when I delete one of the rows, the other row that was highlighted will no longer be highlighted (because we deleted the duplicate, and it’s no longer picked up by the conditional formatting)

I’m a Google Sheets user, so I can give instructions on how to do this in Sheets. But I do not use Excel, so I can’t speak to how to do it there.

To do this in Sheets:

  • Go to Format > Conditional Formatting in the toolbar at the top.

    image
    image720×487 54 KB

    • A “heads up” message will appear saying you’re trying to edit a part of the sheet that’s not intended. Just ignore that, click “don’t show for 5 minutes” and then Ok
      image

  • The conditional format panel will open on the right. Click on the option to “+ Add another rule”.

    • For some reason, if a rule does not exist the first time you do this (which none exist on this sheet by default), it will auto create one. You will know if this happens because all cells that are not empty will suddenly be highlighted. If this happens, just click into the newly created one and edit it instead of creating a new one.
      image

  • Set the Apply to range as the entire Transactions sheet, starting at A2. Pasting in this range should be good enough: A2:R

  • Then, for the format rules, choose Custom formula is from the dropdown box. The custom formula you want to enter is below, but you have to change all instances of the column it’s looking at to the column of your TransactionsID. My TransactionsID is in Column R.

    • Change all instances of column R below to the column that holds your TransactionIDs:
      =IF($R:$R = "", false, IF(COUNTIF($R:$R, $R2) > 1,true,false))
      image
      image322×591 15.8 KB
1 Like
3

Thank you very much. struggled with a similar formula for a long time this morning but nothing is working – yet.

In my case, the last column is AB so my ranges is A2:AB.

The Transaction ID column is “T”

So, I replaced “R” in the formula that you use with “T” as shown below:

=IF($T:$T = “”, false, IF(COUNTIF($T:$T, $T2) > 1,true,false))

This resulted in every row being highlighted.

Some reason, some of the cells in column T are blank and I wonder if that might be causing a snafu.

I’ll keep investigating and let you (and everyone else) know if I find a solution.

Thanks again for sharing your solution.

ScottC.

1 Like
4

Can you go into the formula and delete the double quotes after the second equal sign and type them in manually on the Sheets side? I think when you copied out the formula, it automatically copied out a stylized double quote that is causing issues in Sheets.

Doing this, the formula should hopefully start working.

5

The top formula is the one you entered above. The bottom formula is how it should look. I believe those stylized quotes are throwing things off.

image

6

I spoke (replied) too soon. Your original formula DID work. I saw a lot of highlighted rows and falsely assumed all of the rows were highlighted.

Following your instructions, I deleted the duplicates one by one and it worked perfectly.

So Thank You, again.

FWIW, I’ve been using Tiller for over 2 years. I think it’s a terrific product with fantastic company and forum support. As far as I know, I’ve never had this issue before and I have almost 6,000 transactions. I wonder if it’s related to my bank download or to a new “bug” inTiller or operator error (me) or ??.

Using your Conditional Formatting rule, I’ll start monitoring this. If it continues, I’ll open a Support Request with Tiller.

ScottC

2 Likes
7

I get that duplicate transactions are bad, but I’m curious if a duplicate Transaction ID causes any issues? Like, what is the Transaction ID used for within the tools?

For example, a duplicate Transaction ID could happen if someone manually duplicated the row to create a split transaction, resulting in two rows with differing amounts and categories, but the Transaction ID would be the same. Similarly with creating the other end of a Transfer with a manual account.

8

A good rule of thumb is if the transactions are the same except for the transaction ID, then it’s a problem with the data pulling in and our support team can troubleshoot that.

If the transactions are the same including the transaction ID, it can be human error (where you can reference version history to find any clues on what happened) or there’s an issue with the fill function crashing that our support team can also dive into.

1 Like
9

The transaction ID is used for our support team to help troubleshoot with our data provider. We have a data base where we can enter the transaction ID and get another ID code that we then submit to our data provider if data is pulling incorrectly or duplicated.

1 Like
10

Hello! I’ve been noticing duplicate transactions more often. What is the best way to report this and work with the support team?

11

I’d recommend reaching out to our support team via the chat window in the lower right corner of the Console at https://my.tillerhq.com/

Here are some causes of duplicates: What Should I Do if I See Duplicate Transactions in my Sheet? | Tiller Help Center