+ Reply to Thread
Results 1 to 11 of 11

Finding the first item in a subset

  1. #1
    Registered User
    Join Date
    06-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Cool Finding the first item in a subset

    Hello
    I have been trying to find an answer to this question almost the whole day.. Please help!
    I have a list that kind of looks like this. I sorted it by date, then by location, and then by time. I need to find the earliest item in one location on a given day and display as a list. Any ideas? Thanks!

    date location time
    6/1/2013 A 730
    6/1/2013 A 745
    6/1/2013 A 750
    6/1/2013 B 800
    6/1/2013 B 810
    6/1/2013 C 730
    6/1/2013 C 745
    6/1/2013 C 800
    6/1/2013 C 815
    6/2/2013 A 730
    6/2/2013 A 745
    6/2/2013 A 800
    6/2/2013 A 815
    6/2/2013 B 700
    6/2/2013 B 800
    6/2/2013 B 815
    6/2/2013 C 800
    6/2/2013 C 815

  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: Finding the first item in a subset

    Not real clear.

    Show us what result you expect.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    06-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Smile Re: Finding the first item in a subset

    Ok. This is what I want to see (in bold). I need the first item in each location on a given day, since it is already sorted out by value. Thanks!

    6/1/2013 A 730
    6/1/2013 A 745
    6/1/2013 A 750
    6/1/2013 B 800
    6/1/2013 B 810
    6/1/2013 C 730
    6/1/2013 C 745
    6/1/2013 C 800
    6/1/2013 C 815
    6/2/2013 A 730
    6/2/2013 A 745
    6/2/2013 A 800
    6/2/2013 A 815
    6/2/2013 B 700
    6/2/2013 B 800
    6/2/2013 B 815
    6/2/2013 C 800
    6/2/2013 C 815

  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: Finding the first item in a subset

    OK, do you literally want the cells to be formatted in bold or do you want those cells that are bolded to be displayed in some other location?

  5. #5
    Registered User
    Join Date
    06-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Finding the first item in a subset

    I would rather see them as a list in a different location so I can do further work.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Finding the first item in a subset

    Hi #1 and welcome to the forum,

    See if this Pivot Table does the trick for you.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

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

    Re: Finding the first item in a subset

    Try this...

    Assume the data is in the range A2:C19.

    In cell D2 enter an X.

    Enter this formula in D3 and copy down to D19:

    =IF(B3=B2,"","x")

    This will mark the relavent data rows with an X.

    Now, to aggregate that data...

    Enter this array formula** in E2:

    =IFERROR(INDEX(A:A,SMALL(IF($D$2:$D$19="x",ROW(D$2:D$19)),ROWS(E$2:E2))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy across to G2.

    Format E2 as Date and format G2 as Time.

    Then, select E2:G2 and copy down until you get blanks.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,733

    Re: Finding the first item in a subset

    In the attached file I've shown in stages how you can use formulae to achieve this. Combine A and B in column E, extract the unique values in column G, then split the date and location out in I & J, and finally return the value of the first occurrence in column K.

    Hope this helps.

    Pete
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: Finding the first item in a subset

    ie range data at A2:C19

    start filter formula at E2 (array formula)
    {=IFERROR(INDEX(A$2:A$19,SMALL(IF(MATCH($A$2:$A$19&$B$2:$B$19,$A$2:$A$19&$B$2:$B$19,0)=ROW($A$2:$A$19)-1,MATCH($A$2:$A$19&$B$2:$B$19,$A$2:$A$19&$B$2:$B$19,0)),ROW(1:1))),"")}

    drag right and down

    (see attachment)

  10. #10
    Registered User
    Join Date
    06-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Finding the first item in a subset

    You guys are awesome! Thanks a lot!

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

    Re: Finding the first item in a subset

    You're welcome. We appreciate the feedback!

+ 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