Match data in two columns within date range

[simonpp]

I have a spreadsheet with a date column (A), debits (B) and credits (C). I’m trying to identify where an amount in the debit column appears in the credit column and the dates are within 5 days of each other. Any suggestions will be gratefully received!
Simon

 

[wsjackman]

how do you want them identified?

maybe in d2
somthing like

=if(sumproduct(--(abs(A$2:A$1000-a2)<=5),--(C$2:C$1000=B2))>1,"Yup","Nope")
copy and paste down

 

[paulrit]

its working absolutely fine, but its going top of my head. Could yu pls explain a bit as how its working.

Thanks for your help.

Paul

 

[paulrit]

its working absolutely fine, but its going top of my head. Could yu pls explain a bit as how its working.

Thanks for your help.

Paul

 

[paulrit]

Could somebody pls explain or give me any reference link, how the sumproduct is working here.

Thanks,
Paul

 

[wsjackman]

=if(sumproduct(--(abs(A$2:A$1000-a2)<=5),--(C$2:C$1000=B2))>1,"Yup","Nope")

the use of the sumproduct in this situation counts logical true combinations

the logic formula (abs(A$2:A$1000-a2)<=5) is either true or false. the -- in front of it makes it be either numeric 1 or 0

the logic formula (C$2:C$1000=B2) is treated the same way.
any time a pair of cells in the same row both result in true, you will get a 1*1 or 1 if only one or neither are true the result will be 0


I do not know for sure whether you need the result to be greater than 0 or greater than 1, looking at it again it should probably be greater than 0 , and I do not remember my logic as to why I used greater than 1

 

[paulrit]

its >1, got the logic. Thx for yr help!