+ Reply to Thread
Results 1 to 8 of 8

Sorting Grouped data into date order so that the grouped child follows the parent

  1. #1
    Registered User
    Join Date
    06-10-2021
    Location
    Wellington New Zealand
    MS-Off Ver
    Office 365
    Posts
    10

    Question Sorting Grouped data into date order so that the grouped child follows the parent

    Hi there

    I have an issue sorting grouped "parent" and "child" documents into date order as the "children" do not follow the parent when sorting.

    I have attached an example Excel Spreadsheet. I want the Parent IDs to sort on the Date Column C but not the children IDs. I want the children to "follow" the parent when I sort the parents into date order.

    These are essentially a index to emails with documents attached to them that have been put on a hard copy file, so physically they are arranged from newest to oldest, with attachments behind the parent email. Some documents have dates and some do not (although I could give them a nominal date to match the parent email date and time so as to work as a placeholder). Ultimately, I would like to be able to sort the entire list from oldest to newest and then "reassign" the document ID accordingly.

    E.g.in the attached spreadsheet, when the sort function is used MRG00016 (dated 11 October 2013) and MRG00017 (undated) ought to sort before MRG0007 (24 October 2013) and MRG00008 (undated) but that does not happen.

    Does anyone know how to make the sort function work so that the grouped rows follow each other?

    Cheers
    Mark.
    Attached Files Attached Files

  2. #2
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Sorting Grouped data into date order so that the grouped child follows the parent

    You can use a helper column with this formula in cell K2

    =IF(B2="",B1&ROW(),B2&ROW())

    Drag this down as far as you need. Then you can sort on column K.

    Expand your selection (default in my version) and hide column K after the sort if you want.
    Last edited by Croweater; 06-11-2021 at 08:56 PM.

  3. #3
    Registered User
    Join Date
    06-10-2021
    Location
    Wellington New Zealand
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Sorting Grouped data into date order so that the grouped child follows the parent

    Thanks for that.

    Sorry, I was not very clear regarding my dataset.

    What you sugggest works provided that there is only 1 attachment to the preceding email or document, but unfortunately in some cases there might be ten attachments to one email or document.

    I have attached an amended spreadsheet to demonstrate what I mean.

    The total number of documents is likely to exceed 5,000.

    Do you (or anyone else) have a solution so that all of the children follow the parent when sorted?

    Many thanks
    Attached Files Attached Files

  4. #4
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Sorting Grouped data into date order so that the grouped child follows the parent

    Well, it depends how you define 'children'. Sometimes we have a blank date, sometimes a 'parent' id in column J. Are there any other definitions?

    Has document type got anything to do with it? If so it might be better to do a test on that column.

    Here is a simple formula that may work.

    =IF(OR(J2<>"",B2=""),K1+1,B2*10000+ROW())
    Last edited by Croweater; 06-12-2021 at 09:17 PM.

  5. #5
    Registered User
    Join Date
    06-10-2021
    Location
    Wellington New Zealand
    MS-Off Ver
    Office 365
    Posts
    10
    Quote Originally Posted by Croweater View Post
    Well, it depends how you define 'children'. Sometimes we have a blank date, sometimes a 'parent' id in column J. Are there any other definitions?

    Has document type got anything to do with it? If so it might be better to do a test on that column.
    The child Parent ID defines the parent email or document according to the parent DOCID. It ldoes not matter which type of document it is. Sometimes the parent or children or both are not dated because no date appears on the physical document in the file. All that matters is that the documents that have a Parent ID travel with the parent's DocID when the parents are sorted into date order.

    Does that help?

  6. #6
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Sorting Grouped data into date order so that the grouped child follows the parent

    OK. Then that formula above can be simplified even further;

    =IF(J2<>"",K1+1,B2*10000+ROW())

    This assumes less than 10000 rows of data.

    Also assumes ascending order (earliest date first) of course as the children are '+1' on the parent.
    Last edited by Croweater; 06-12-2021 at 09:33 PM.

  7. #7
    Registered User
    Join Date
    06-10-2021
    Location
    Wellington New Zealand
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Sorting Grouped data into date order so that the grouped child follows the parent

    Thank you.

    That seemed to work.

    However, is it also possible to factor in the DATE and TIME for the parent emails rather than just the DATE, and still keep the child/children with the parent when the list is sorted?
    For example a person may send several emails on the same day to the same person with the same topic at different times, with different attachments to each email. It's useful to review these in chronological order, which requires sorting by time as well as date.

    I am not quite certain what IF / OR function is testing in plain language, so I cannot think of how to factor in the "time" colomn in this funciton.

    Cheers
    Mark.

  8. #8
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Sorting Grouped data into date order so that the grouped child follows the parent

    You could try something like this in K2

    =IF(J2<>"",IF(C2<>"",K1,K1+1),B2*10000+ROW())

    and this in L2

    =IF(J2="",0,B2+C2)

    drag both down and then sort on column K ascending then column L ascending.

+ 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. Rank Order Grouped by Date and Time
    By dslaugh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2020, 10:45 AM
  2. Sorting grouped data
    By dmendes11 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-29-2013, 07:16 PM
  3. Sorting grouped data
    By dmendes11 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-29-2013, 06:21 AM
  4. Sorting data that is grouped together
    By jbmerrel in forum Excel General
    Replies: 0
    Last Post: 05-09-2012, 10:20 AM
  5. Sorting grouped data
    By cosmotion in forum Excel General
    Replies: 3
    Last Post: 04-22-2008, 01:15 PM
  6. [SOLVED] Sorting Grouped Data?
    By Dan in forum Excel General
    Replies: 2
    Last Post: 06-16-2005, 06:05 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