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

Lyryx

New Member
Joined
Aug 18, 2015
Messages
45
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
C2020-01-0515.000100.00Withdrawal
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Watch MrExcel Video

Forum statistics

Threads
1,114,194
Messages
5,546,489
Members
410,742
Latest member
WalterSil
Top