Formula to Find matching Absolute Values in a Range of Dates?

Lyryx

New Member
Joined
Aug 18, 2015
Messages
45
Hi Everyone,

I've been developing a project for work that will analyse bank accounts to determine the value of a project. The problem is some of our projects have inter-account transactions. In order to determine the proper worth of the project we have to find any transfers between the accounts and remove them. This is a tedious process of us going through the data and looking for values in from account A that match the values leaving account B or vice versa.


To solve this I created two temporary Formula... it's been a bit of a band-aid solution however because it only identifies transfers that might have occurred on the exact same transaction date when sometimes a transfer might originate in Account A on a Friday and then after 3 business Days come out of of Account B. So this formula ends up ignoring transfers that occurred over the course of a week in business days.

So I was hoping to get a similar formula but have it look for values within a week of each other ... I'm not sure if that is even possible though which is why I am posting this message here.

The formulas are as follows:

HTML:
=IF(COUNTIF([Unique ID],[@[Unique ID]])>1,"Check For Transfer","")
and
HTML:
=CONCATENATE([@[Transaction Date]],"-",ABS([@[Withdrawal/Debits]]-[@[Deposits/Credits]]))

Where:
  • The first formula Counts all instances of Unique IDs, if any return more than once than it COULD be a transfer... however sometimes it's a cancelled check out of the same account...
  • [Unique ID] creates a text value combining the date and absolute value of the ranges of data.

I realize this might be more complicated or even impossible but if a solution exists I know it will be found here.


Once again the goal is to Identify transfers between two or more accounts, a transfer occurs when for example $100 leaves Account A and then enters Account B on the same day or within 5 business days.
The formula I am currently using only looks for the same day so I was wondering if it would be possible to get it to look for matches within 5 days or a week.

Thanks in advance! :)
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,574
Office Version
  1. 2010
Platform
  1. Windows
You could divide the date by 10 and round it down and then multiply it back up again. This will make all the dates in a 10 day period identical: e.g.
Code:
=10*ROUNDDOWN(NOW()/10,0)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,237
Messages
5,576,899
Members
412,753
Latest member
Coach_Olson
Top