I need a formula to check for duplicate values across a 10 day period


New Member
Aug 18, 2015
I have a workbook that analyzes 3-5 years worth of bank information, I need to identify when money is being transferred from one account to another.

IE. I move $10.00 out of account A and put it into account B.

Currently I have a formula that will Identify duplicates that occur on the same day, but sometimes bank delays occur and that same transfer will happen anywhere from 2 to 10 days after.

Here is a sample of the applicable categories in my workbook
Account #DateDebitsCreditsRunning TotalTransaction DescriptionTransfer?
A2020-01-0110.00010.00Cash DepositCheck for Transfer
B2020-01-01010.000.00Cash WithdrawalCheck for Transfer
D2020-01-10015.0050.00Payment Thank you

My current formula in use is a combination of two hidden cells. The first creates a unique ID by adding the absolute value of the Debits and Credits Column to the Date to create a unique ID
The second formula then scans all of the Unique ID's for any matches. Which would likely be a transfer (not always the case). If it finds a matching ID in it's respective column then it will return a "check for Transfer" in it's cell.

So the formula would catch the transfer from account A to account B but would miss the transfer from account C to account D

I'd love to have some formula or method to identify both potential transfers that are occurring.

Thanks in advance

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Watch MrExcel Video

Forum statistics

Latest member