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