Identify credits and debits within a week of each other

[simonpp]

Hi,

I have a worksheet with multiple bank statement all combined together and sorted by date.
Column E has the date, Column H has Debits and Column I has Credits, Row 1 has headers.

I am trying to find a way of identifying debits and credits within a week of each other where the amount is the same. E.g. where a £1,234 credit (in column I) correlates with a £1,234 debit in the week before or after (in column H). I am trying to eliminate matches where

The bit I'm struggling with is accurately searching within a week of the date of the credit to find matching debits.

=IF(COUNTIF(H:H,I4)>0,"Found","Not Found") finds matches but within the whole range.

This is what I've been playing around with although unreliably.
=IF(SUMPRODUCT(--(ABS(E$2:E$14031-E2)>=7),--(H$2:H$14031=I2))>1,"Yes","No")

If it makes any difference I'm using Excel 2003.

 

[pbornemeier]

This formula might work:

=IF(H2>0,SUMPRODUCT(--(E2<$E$2:$E$199+7),--(E2>$E$2:$E$199-7),--(H2=$I$2:$I$199)),"")

You would probably also want another column with the above formula changed to IF(I2>0 .....