Finding duplicate Transaction ID's

[1Email2RuleThemAll]

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.
  

  
  • 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
    

• 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.
    

• 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))