formulas that matches data and subtract dates
 

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

formulas that matches data and subtract dates
 

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

formulas that matches data and subtract dates
 

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 DataSortColumnDAscending
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


--
Regards

Roger Govier


"Sting17" wrote in message
...
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

formulas that matches data and subtract dates
 

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

"Roger Govier" wrote:

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 DataSortColumnDAscending
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


--
Regards

Roger Govier


"Sting17" wrote in message
...
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

formulas that matches data and subtract dates
 

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

formulas that matches data and subtract dates
 

Hi Sting

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

Proceed as before

--
Regards

Roger Govier


"Sting17" wrote in message
...
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

"Roger Govier" wrote:

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 DataSortColumnDAscending
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


--
Regards

Roger Govier


"Sting17" wrote in message
...
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

formulas that matches data and subtract dates
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sting17" wrote:
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.