Formula To Return First and Last Air Dates
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Formula To Return First and Last Air Dates

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,868
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com