+ Reply to Thread
Results 1 to 3 of 3

Earliest and Latest Time where cell contains certain text

  1. #1
    Registered User
    Join Date
    04-23-2013
    Location
    MN
    MS-Off Ver
    Excel 2007
    Posts
    1

    Earliest and Latest Time where cell contains certain text

    I am starting from a worksheet that has three columns: A - Name, B - Time and C - Volume. There are up to 208 unique names, but each may have between 1 and 100 rows, with different times - a total of ~1600 different times. I would like to create a new worksheet that gives me the total count per name and then the earliest and latest times for each name. I already have a master list of the names (Sheet 2 column A) and figured out how to do the count (column B). How can I get the earliest time (column C) and the latest time (column D) for each name?

    I've tried max and min but can't figure out how to limit the range for each line in Sheet2 to ONLY consider those in column A of Sheet1 that match the name in column A of Sheet 2 (my master list).

    Sample of what I'm looking for:

    Name Volume EarliestSubmissionTime LatestSubmissionTime
    ABC Corp 25 10:07 AM 4:32 PM


    Thanks!

  2. #2
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Earliest and Latest Time where cell contains certain text

    Hello,

    What you can do is using an Array formula like

    =SMALL(IF('MasterList'A2='List'A:A,'List'C:C),1)

    and hold Ctrl-Shift then hit Enter (If you see the formula warped in { }, then you did it right). The formula above assumes that the name you want to search for is in A2 (ABC Corp for e.g), 'List'A:A is the column where you have the name, 'List'C:C is the column where you have the EarliestSubmissionTime.
    Similarly, to find the LastestSubmissionTime, you can use

    =LARGE(IF('MasterList'A2='List'A:A,'List'D:D),1)

    For better results, please provide a sample file.
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Earliest and Latest Time where cell contains certain text

    One way could be using MIN(IF---MAX(IF, which are ARRAY formulas.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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