+ Reply to Thread
Results 1 to 3 of 3

Group by ID and Sort by Highest first & Export thebest from each group

Hybrid View

  1. #1
    Registered User
    Join Date
    12-22-2023
    Location
    Yorkshire, England
    MS-Off Ver
    2016
    Posts
    8

    Group by ID and Sort by Highest first & Export thebest from each group

    I would like help in trying sort and export some data from a massive spreadsheet. There is a sample XLSX attached.

    1 First step is to group each ID together.

    2 Next I want to sort a month (April in the axample file) with the highest figure for each group.
    So in ID 101 Dawn would be the highest with 116.
    Or in ID 102 Lola would be top.
    3 The bit I cannot do is just show the Row for each ID's Highest figure.

    4 The final bit I would like to do is export the data, to a new worksheet. This would be for each ID the row which has the highest figure, so 1 row per ID. Again not sure how to do that.

    (subsequent edit) If there were 2 people with a joint highest figure then they would both(all) need to be listed at point 3.

    (2nd edit ) Glenn that is good in how it shows the highest person but I trying to see the whole row of information for each resulting highest person per ID. I had been soring by ID then Month and removing duplicates - from ID column.
    This works except when there are joint highest in any grouped ID as it just show the first named of the two. I have attched Chris2.xlsx and in ID 103 have 2 names with a joint highest number now.

    Thanks if you can help on something I have spent a bit of time at not being able to do properly.
    Attached Files Attached Files
    Last edited by no9kris; 12-22-2023 at 10:30 AM. Reason: Joint highest additional comment

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Group by ID and Sort by Highest first & Export thebest from each group

    1. Set up a named range (called Rng, CTRL- F3 to view edit) to capture the last row of the IDS:
    =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

    2. To return the unique IDs, sheet2, a2, copied down:
    =IFERROR(INDEX(Rng,MATCH(1,INDEX(--ISNA(MATCH(Rng,A$1:A1,)),),)),"")

    3. To return the names, shhet 2, B2, copied across and down:
    =IF($A2="","",IFERROR(INDEX(Sheet1!$B:$B,AGGREGATE(15,6,ROW(Sheet1!$C$2:$C$20)/(Sheet1!$C$2:$C$20=AGGREGATE(14,6,Sheet1!$C$2:$C$20/(Sheet1!$A$2:$A$20=Sheet2!$A2),1)),COLUMNS($A2:A2))),""))

    4. I introduced a tied result, as you said this could happen, but did not include one in your sample.

    5. Refer to the file.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Registered User
    Join Date
    12-22-2023
    Location
    Yorkshire, England
    MS-Off Ver
    2016
    Posts
    8

    Re: Group by ID and Sort by Highest first & Export thebest from each group

    Thanks Glen for the quick responce. Will defo try your solution.

    Chris

+ 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: 14
    Last Post: 01-12-2017, 01:41 PM
  2. sort by group and total by each group
    By mheinemann in forum Excel General
    Replies: 3
    Last Post: 04-30-2015, 11:48 AM
  3. Replies: 1
    Last Post: 04-18-2014, 05:54 PM
  4. Replies: 1
    Last Post: 02-10-2014, 10:26 PM
  5. Replies: 1
    Last Post: 10-19-2012, 07:55 AM
  6. Sort a group of names based on the group total
    By ron2k_1 in forum Excel General
    Replies: 3
    Last Post: 08-13-2010, 01:16 PM
  7. Replies: 3
    Last Post: 03-08-2005, 05:06 PM

Tags for this Thread

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