Formula To Return First and Last Air Dates

[Harley]

Column A contains different commercial titles (Nike 1, Nike 2, Nike 3, etc.). Column B contains a single date that the commercial aired on. For example:

A B
Nike 1 01/02/02
Nike 1 03/01/02
Nike 1 02/26/02
Nike 2 12/31/01
Nike 2 5/14/01
Nike 3 6/12/01
Nike 3 12/13/01
Nike 3 11/4/01

How can I create a formula that will return a first air date and last air date for each commercial? I need the formula to look at all the dates in column B for each different commercial and return the earliest air date and the most recent air date.

In the above example, the formula would return for "Nike 1" First Air Date - 1/2/02, Last Air Date - 3/1/02

I could sort by A then by B and manually look up the first and last air dates but that would take too long considering there are thousands of dates to scroll through for each commercial.

[ This Message was edited by: Harley on 2002-04-05 09:10 ]

[ This Message was edited by: Harley on 2002-04-05 09:11 ]

[ This Message was edited by: Harley on 2002-04-05 09:11 ]

[Jay Petrulis]

On 2002-04-05 09:09, Harley wrote:
Column A contains different commercial titles (Nike 1, Nike 2, Nike 3, etc.). Column B contains a single date that the commercial aired on. For example:

A B
Nike 1 01/02/02
Nike 1 03/01/02
Nike 1 02/26/02
Nike 2 12/31/01
Nike 2 5/14/01
Nike 3 6/12/01
Nike 3 12/13/01
Nike 3 11/4/01

How can I create a formula that will return a first air date and last air date for each commercial? I need the formula to look at all the dates in column B for each different commercial and return the earliest air date and the most recent air date.

In the above example, the formula would return for "Nike 1" First Air Date - 1/2/02, Last Air Date - 3/1/02

I could sort by A then by B and manually look up the first and last air dates but that would take too long considering there are thousands of dates to scroll through for each commercial.

[ This Message was edited by: Harley on 2002-04-05 09:10 ]

[ This Message was edited by: Harley on 2002-04-05 09:11 ]

[ This Message was edited by: Harley on 2002-04-05 09:11 ]

Hi Harley,

I used A1:B8 for your data supplied.

In C1 I placed the text, "Nike 1"
In C2, "Nike 2"
In C3, "Nike 3"

In D1 I placed the following array-entered formula

=MIN(IF($A$1:$A$8=C1,$B$1:$B$8))

You must enter this with Ctrl+Shift+Enter, rather than just Enter. You will see Excel generated braces {} surrounding the formula if done correctly.

In E1, I array-entered the following

=MAX(IF($A$1:$A$8=C1,$B$1:$B$8))

I copied down the range to fill in the rest.

Please adjust your range as needed.

If you need this with your text in one cell, use something like:

=C1& " first aired on " & TEXT(MIN(IF($A$1:$A$8=C1,$B$1:$B$8)),"d/m/yy") & ", last aired on " & TEXT(MAX(IF($A$1:$A$8=C1,$B$1:$B$8)),"d/m/yy")

also array enetered.

Bye,
Jay

[Aladin Akyurek]

On 2002-04-05 09:09, Harley wrote:
Column A contains different commercial titles (Nike 1, Nike 2, Nike 3, etc.). Column B contains a single date that the commercial aired on. For example:

A B
Nike 1 01/02/02
Nike 1 03/01/02
Nike 1 02/26/02
Nike 2 12/31/01
Nike 2 5/14/01
Nike 3 6/12/01
Nike 3 12/13/01
Nike 3 11/4/01

How can I create a formula that will return a first air date and last air date for each commercial? I need the formula to look at all the dates in column B for each different commercial and return the earliest air date and the most recent air date.

In the above example, the formula would return for "Nike 1" First Air Date - 1/2/02, Last Air Date - 3/1/02

I could sort by A then by B and manually look up the first and last air dates but that would take too long considering there are thousands of dates to scroll through for each commercial.

[ This Message was edited by: Harley on 2002-04-05 09:10 ]

[ This Message was edited by: Harley on 2002-04-05 09:11 ]

[ This Message was edited by: Harley on 2002-04-05 09:11 ]

Lets say that A1:B9 houses your sample data including added headings:

{"Titles","Comm Dates";
"Nike 1",37258;
"Nike 1",37316;
"Nike 1",37313;
"Nike 2",37256;
"Nike 2",37025;
"Nike 3",37054;
"Nike 3",37238;
"Nike 3",37199}

The big numbers are just dates in the internal representation that Excel uses, so don't be alarmed.

Prerequisite: The data must be sorted on column A.

Create a unique list of titles (using e.g., Advanced Filter) in D from D2 on including the column heading "Titles".

In D1 enter:

=MATCH(9.99999999999999E+307,B:B)

In E2:F2 enter:

{"First Air Date","Last Air Date"}

In E3 enter:

=OFFSET($B$2,MATCH(D3,OFFSET($A$2,0,0,$D$1-1,1),0)-1,0,1)

In F3 enter:

=OFFSET($B$2,MATCH(D3,OFFSET($A$2,0,0,$D$1-1,1))-1,0,1)

Select E3:F3 and copy down as far as needed.

This is what you get to see in the results area, that is, D1:F5 regarding the sample data:

{9,"","";
"Titles","First Air Date","Last Air Date";
"Nike 1",37258,37313;
"Nike 2",37256,37025;
"Nike 3",37054,37199}

You don't need to adjust the formulas when you add to or delete from the data area titles and dates associated with them.

Aladin

[Harley]

Jay...that works great...this will save loads of time...thanks