Identifying Synergies by both date and location

[g.s.d]

Hello All,

new to the forum and need some help with a tricky problem....please help!

Assume Column B is a shipment ID.

Assume Column C is the ship from location.

Assume Column D is the ship to location

Assume Column E is the week of the year that product shipped from the ship from location

Notice that LOCALs can both be ship from locations and ship to locations


Assume ShipmentX leaves LOCAL1 and ships to LOCAL2 in week 1 of the year. then if a shipmentY is shipping from LOCAL2 to LOCAL1 +/- 1 week from the week that ShipmentX leaves LOCAL1, that is a synergy. My goal is to identify every time there is an opportunity like that for each individual shipment, shown as 1 (Yes) or 0 (No).

[tim201110]

quite a lot of opportunities
for just one
=SUMPRODUCT(--(((C409=D410:$D$6876)*ROW(D410:$D$6876))=((D409=C410:$C$6876)*ROW(D410:$D$6876))),--((C409=D410:$D$6876)*ROW(D410:$D$6876)>0),--(ABS(E410:$E$6876-E409)<=1))