Can you reformulate to make it read the first two letter of the pairing code:
"1P" of the "1Pa"? I can have:

Detroit 1Pa 01/01/01
Detroit 1Pb 03/01/01

These would be paired events even thought the letter"a" and "b" are
different. So basiically, it would be everything that was "Detroit" and "1P"
that I would want to apply the calculation to. Possible? Thanks.

"Max" wrote:

Here's one crack at this ..

Assuming data in cols A to C, from row2 down

Put in D2, and array-enter the formula,
ie press CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=IF(SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2)*($A$2:A2< "")*($B$2:B2<""))=2,C2-MIN(IF(($A$2:A2=A2)*($B$2:B2=B2),$C$2:C2)),"")
Copy D2 down as far as required

Col D will return the number of days
between the 2 events on the "2nd event" line, viz.:

Detroit 1Pa 06-04-2004
Detroit 1Pa 23-06-2004 78
Detroit 1Pb 01-08-2004
Detroit 1Pb 05-11-2004 96

And if you want the results in decimal weeks (ie # of days/7),
then just put instead in D2, array-entered as before, and fill down:
=IF(SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2)*($A$2:A2< "")*($B$2:B2<""))=2,(C2-MIN(IF(($A$2:A2=A2)*($B$2:B2=B2),$C$2:C2)))/7,"")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sting17" wrote:
I am trying to combine a formula that does two different things; matches
cities, a code for the city, and if it has a match, calculate the difference,
in weeks between the two events.
ex.
Detroit, 1Pa, 04/06/04
Detroit, 1Pa, 06/23/04
Detroit, 1Pb, 08/01/04
Detroit, 1Pb, 11/05/04

I want to match the cities, and if they have a matching code, find out the
time in weeks between the two event dates. Is this possible, and if so,
HOW??? THANKS