formulas that matches data and subtract dates

[Guest]

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

 

[Guest]

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,"")

 

[Roger Govier]

Hi Sting

One way
Copy your data to another sheet - click on sheet tab, hold down Control,
as you drag your cursor to the right. This will create a duplicate of
the sheet.
On this copy of the data, in column D enter
=A1&B1
Copy down column D for the extent of your data.
Mark the whole block of data, then Data>Sort>ColumnD>Ascending
in column E enter the following in cell E2
=IF(D2<>D1,"",(C2-C1)/7)

You show your data as in consecutive pairs, but I guess in reality the
data may be scattered throughout the table. The sort on the concatenated
filed will bring all of the like data together

 

[Guest]

Thank you. I tried both and both worked. One issue that I found was that I
need to sort by the "P1" and "P2" of the P1a and P2a. The "P1" data is the
pairing data, the "a", "b", and "c" just refers the time of the month it
happened. I guess that I have to create another column to read that data as
"P1" and "P2", then use that as the sort?
Thank you very much.

Sting

 

[Guest]

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.

 

[Roger Govier]

Hi Sting

If you want to omit the "a", "b" and "c" make the concatenation formula
instead
=A1&LEFT(B1,2)

Proceed as before

 

[Guest]

Assuming that the source data is already/always in pairs from row2 down, eg:

Detroit 1Pa 06-04-2004
Detroit 1Pa 23-06-2004
Detroit 1Pb 01-08-2004
Detroit 1Pb 05-11-2004
Miami 1Pa 06-04-2004
Miami 1Pa 23-06-2004
Chicago 1Pb 01-08-2004
Chicago 1Pb 05-11-2004
etc

Then perhaps this might suffice in D2, copied down:
=IF(OR(A2="",B2=""),"",IF(MOD(ROW(),2)=1,C2-C1,""))
to return the number of days
between the 2 events on every "2nd event" line