+ Reply to Thread
Results 1 to 4 of 4

How to extract only the Start Date (earliest) and finish date (latest) of a unique product

  1. #1
    Registered User
    Join Date
    02-10-2016
    Location
    Anaheim, CA
    MS-Off Ver
    Excel 2010
    Posts
    20

    Question How to extract only the Start Date (earliest) and finish date (latest) of a unique product

    Hello there,

    I have an excel date with different products and some of them are repeated.

    (Please see attached file to look at the table)products.xlsx

    On the first column, I created the formula: =IF(COUNTIF($B$1:B1,B2)>0,"",MAX($A$1:A1)+1) to give it an index number. Next to it is the product name and then start date and finish date

    On another sheet I'm tracking ONLY unique products: on the first column with unique products, I wrote the formula to only look up unique product names:
    =IFERROR(VLOOKUP(ROWS(G$2:$H2),$A:$A:$B:$B,2,FALSE),"") and that is how I came up with the table below:

    UNIQUE PRODUCTS Start Date Finish date
    product 1
    product 2
    Product 3
    Product 4
    Product 5

    Now my question is, what formula should I use to grab the start date (earliest date) and the finish date (latest date) of a unique product that was in the previous table?
    so for example Product 1, I want the start date to show 2/16/2016 and I want the end date to show 4/1/2016 on the 2nd table with unique products.

    Thanks for your help!
    Attached Images Attached Images

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to extract only the Start Date (earliest) and finish date (latest) of a unique pro

    So you only want the dates that correspond to the product that has the index number?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    02-10-2016
    Location
    Anaheim, CA
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: How to extract only the Start Date (earliest) and finish date (latest) of a unique pro

    Not exactly. I want the Start Date of the first Product 1 and the Finish date of the last Product 1 (if more than one). I only had to generate the index number so that I can vlookup unique values

    So for Product 1 (which has 4 entries) I want to grab 2/16/2016 for start date and 4/1/16 for the finish date

    Product Start Finish
    product 1 2/16/2016 3/1/2016
    product 1 2/16/2016 3/1/2016
    product 1 2/8/2016 3/8/2016
    product 1 3/1/2016 4/1/2016
    Last edited by egrospe17; 02-10-2016 at 07:20 PM.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to extract only the Start Date (earliest) and finish date (latest) of a unique pro

    Assumes the data is sorted/grouped together by product as is demonstrated in your sample file.

    For the start date...

    =VLOOKUP(G2,B$2:D$14,2,0)

    Format as Date then copy down as needed.

    For the finish date...

    =LOOKUP(2,1/(B$2:B$14=G2),D$2:D$14)

    Format as Date then copy down as needed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 11-25-2015, 03:31 PM
  2. Replies: 1
    Last Post: 10-08-2015, 11:06 AM
  3. Does a task start or finish date fall within 3 month range of a specific date
    By jamesmcgallan in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-19-2015, 07:46 AM
  4. [SOLVED] Returning Earliest and Latest Date for a Unique ID
    By chicity26 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-26-2014, 09:39 AM
  5. Calculate start date from finish date minus 14 working hours
    By PietBom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2014, 07:23 PM
  6. Getting the earliest and latest date in Pivot Table
    By dluhut in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-02-2013, 01:54 PM
  7. Make date ranges for graph (choose start and finish date)
    By zeko90 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-23-2013, 09:13 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1