none
sorting groups of data without changing the data within the groups, but not by using the "groups" facility in Excel RRS feed

  • Question

  • Hi,
    I hope someone might be able to help me.

    I am trying to sort entire groups by one cell in each group.
    I can't figure out how to maintain the integrity of the groups.

    At first I set up my data into groups using the Groups feature of Excel, but the sort function does not operate on whole, complete groups.
    So I removed the groups outlines.

    I would like to sort the groups based on data on every 8th row, first column, in alphabetical order without changing the data below that header row and in the 7 rows subsequent rows of that group.

    I have tried to use MS Word, but don't see a way to do it there as well, and the random and inconsistent self-formatting that Word does drives me nuts.

    Is there a way to sort the groups while maintaining the integrity of the groups? Or would I be better off doing this in Access? I imported the raw data into Access, but again, do not see how to sort groups of data by one field in each group. If necessary, I could use Access for the sorting procedure then send the data back to Excel for formatting for Documents To Go. Whatever is easiest. (hmm, it appears that Access files are not viewable in Documents To Go.)

    My goal is to have an alphabetical list of groups of text data that will be in .xls format so I can read it on my old Blackberry in the Documents To Go app.

    The file will continue to get larger, perhaps as many as 100 or more of these groups of text data. Right now there are about 50 of these "groups" of data, about 500 rows.

    Since there is no attachments option here, here is the format of the groups:

    1A-1F merged for group header (this is the column I want the groups of data sorted by - the first word in this cell is the sort key)
    2A empty, 2b-2f column headers
    3A-7A row headers
    3b,4b,5b,6b,7b - 3f,4f,5f,6f,7f text data
    8a empty, 8b-8c merged for text, 8d-8e merged for text, 8f empty

    9a through 16f are a repeat of the above format with different text data and header text data
    repeat for another 9 rows, ad nauseum.

    Thanks for any help you can provide.
    --
    Chip

    Below is a script I was given that is supposed to do the job, but it doesn't. What happens when I run it? The script runs but does not change anything. It does not actually sort the data groups, it apparently has no effect on the data whatsoever.

    Sub YoSoyMuyPeludo()
    
    Dim wsStart As Worksheet: Set wsStart = Sheets("Start")
    Dim wsFilter As Worksheet: Set wsFilter = Sheets("Filter")
    Dim wsEnd As Worksheet: Set wsEnd = Sheets("End")
    
    wsFilter.Range("A2:A5000").Clear
    For i = 1 To wsStart.Range("A" & Rows.Count).End(xlUp).Row Step 8
        If InStr(1, wsStart.Cells(i, 1), ":") Then
            wsFilter.Range("A" & Rows.Count).End(xlUp).Offset(1) = wsStart.Cells(i, 1)
        End If
    Next
    
    With wsFilter.Sort
        .SetRange Range("A2", Range("A" & Rows.Count).End(xlUp))
        .Apply
    End With
    
    PasteRow = 1
    
    For i = 2 To wsFilter.Range("A" & Rows.Count).End(xlUp).Row
        For r = 1 To wsStart.Range("A" & Rows.Count).End(xlUp).Row Step 8
            If wsStart.Cells(r, 1) = wsFilter.Cells(i, 1) Then
                wsStart.Range("A" & r, wsStart.Range("F" & r + 7)).Copy wsEnd.Range("A" & PasteRow)
                PasteRow = PasteRow + 8
                Exit For
            End If
        Next
    Next
    
    wsEnd.Cells.EntireColumn.AutoFit
    wsEnd.Activate
    
    End Sub
    

    Tuesday, July 28, 2015 8:54 PM

Answers

  • Thank you for your help, I have found the way to make it work, and it doesn't require any scripting.

    1. In the ribbon on the Data tab, in the lower right corner of the Outline section there is a small arrow, click on it. Then uncheck the two check boxes - Summary Rows, Summary Columns. Click on OK.

    2. Now make your groups, but do NOT select the row that acts as the group header/title. In my screenshot above you can see my groups are 8 rows each, with the first row of each groups holding the group header/title data. I selected the 7 rows below that row, then click on Group, then click on OK, and Excel will add the header/title row to your selection for you, whether you want it or not is beside the point, it will add it anyway.

    3. After you have created all your groups just select all your data and then click on the Sort button, sort on, in my example, column A, and the entire data set is sorted by that header/title row, and the 7 rows in each groups are not touched, but stay with the header/title row. Very nice!

    Notice: I did not have to insert any blank rows between groups, this is because I unchecked the two check boxes in the Outline section of the Data tab.

    Also, if you have any merged cells you may get the error box telling you about unequal merged cells. It's best to unmerge any merged cells, and live without them.

    I hope this helps other people who have asked this question in many forums, I see in search results that this is something that has been asked many times on many forums, and there seems to be many ways to deal with it, but why bother with scripts when it's not necessary?

    At any rate, that solved the problem.

    • Marked as answer by ChipWiegand Thursday, August 6, 2015 9:56 PM
    Thursday, August 6, 2015 9:56 PM

All replies

  • Re:  sorting groups

    If you set up your groups similar to that shown below, you could use the Excel built-in sort.
    Even adding a column on the left with the rows filled in would do. 
    [Edit]... The new column would be deleted after sorting.
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)



    [EDIT] - AUGUST 03, 2015
    I used this technique on the "Start" worksheet in the workbook you posted. 
    It required 10 lines of vba code, followed by a manual sort.
    You must remove merged cells before excel will allow a sort.
    See below.
    '---
    Jim Cone



    • Edited by James Cone Monday, October 31, 2016 11:32 PM
    Tuesday, July 28, 2015 11:43 PM
  • Hi ChipWiegand,

    Could you provide the screenshot with sample data that you want to sort?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 30, 2015 2:32 AM
    Moderator
  • Thank you for the interest, below is the screenshot.

    screenshot

    Thursday, July 30, 2015 3:50 AM
  • Hi ChipWiegand,

    This is the original data or the result data? What’s the original data or the result data like?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 30, 2015 5:18 AM
    Moderator
  • That is the original data. My goal is to have it sorted by the Spanish verb. In those 3 "groups" in the screenshot they are sorted by the English translation, but that is not the case for all the data. I add more data to this file often, so there is not sort order to it. If possible, I would like to have a script like the one provided, but one that actually works - sorting the "groups" by the Spanish verb. I suppose I could split the Spanish verb and the English translation of that verb into two cells on the same row, if that would make it any easier.
    Thursday, July 30, 2015 2:03 PM
  • Hi ChipWiegand,

    For your requirement, you could get body data of each group, then sort the data.

    The simple sample code that you could refer to:

    ub SortTest()
    Dim r As Range
    Dim c As Excel.Range
    Dim i As Integer
    Dim ws As Excel.Worksheet
    Dim num() As Integer
    Set ws = ActiveSheet
    ws.Sort.SortFields.Clear
    Sort.SortFields.Add Key:=Range("A3"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Cells.SpecialCells(xlCellTypeBlanks)
    ReDim num(r.Cells.Count)
    i = 1
    For Each c In r
    num(i) = c.Row
    i = i + 1
    Next c
    For i = 1 To UBound(num) Step 2
    Range("A" & (num(i) + 1), "F" & (num(i + 1) - 1)).Select
    With ws.Sort
    .SetRange Range("A" & (num(i) + 1), "F" & (num(i + 1) - 1))
    .Apply
    End With
    Next i
    End Sub

    Regards

    Starain

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 31, 2015 5:37 AM
    Moderator
  • Thank you for your assistance, I am getting this error when I run the code - Object required (Error 424)

    in the debug window there is a yellow highlight around this line -

    sort.SortFields.Add Key:=Range("A3"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    with a yellow arrow pointing to the second of those lines.

    I don't know what an object qualifier is, so I don't know what to do to correct this.

    Friday, July 31, 2015 7:45 PM
  • Hi ChipWiegand,

    What’s the version of your excel? The Sort object is added in Excel 2007.

    # Sort Object (Excel)

    https://msdn.microsoft.com/en-us/library/office/ff821877.aspx

    You may try it with the sample code that the article provided and check the result.

    On the other hand, you may share a sample file on the OneDrive.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 3, 2015 1:34 AM
    Moderator
  • I am using Office 2010.

    I was just reviewing the previous posts and it appears there may be a misunderstanding of what results I want to get. In your post with the code, you show an image of the results of your code, but the data is not sorted in the format I am asking for.

    In your image the two rows that act as header rows both have the exact same data - prejuntar. But in the image of data I provided the three rows of header data are not the same, they are different.

    In your image of results the rows under the header row are sorted. That is not what I want.

    In my original post is this line - I would like to sort the groups based on data on every 8th row, first column, in alphabetical order without changing the data below that header row, in the 7 subsequent rows of that group. (I did correct two insignificant, unimportant errors in that quote.)

    So, in summary, all the 7 rows of data are to be left as is, not changed in any way. The sorting of the spreadsheet data is to occur on the header row with the spanish verb-english translation.

    I don't know what the onedrive is, and i don't see any link to it here, so the spreadsheet is available here - http://wiegand.org/d8L1/SpanishVerbs.xlsx
    Monday, August 3, 2015 3:08 PM
  • Hi ChipWiegand,

    I think, you could read each group header data and store in a temp worksheet, then sort the header data, next, you could iterate the sort result and cut the corresponding whole group data to the temp worksheet (one to one), after that, you could cut all groups data back to the original worksheet.  

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by ChipWiegand Thursday, August 6, 2015 9:42 PM
    • Unmarked as answer by ChipWiegand Thursday, August 6, 2015 9:42 PM
    Wednesday, August 5, 2015 8:28 AM
    Moderator
  • Thank you for your help, I have found the way to make it work, and it doesn't require any scripting.

    1. In the ribbon on the Data tab, in the lower right corner of the Outline section there is a small arrow, click on it. Then uncheck the two check boxes - Summary Rows, Summary Columns. Click on OK.

    2. Now make your groups, but do NOT select the row that acts as the group header/title. In my screenshot above you can see my groups are 8 rows each, with the first row of each groups holding the group header/title data. I selected the 7 rows below that row, then click on Group, then click on OK, and Excel will add the header/title row to your selection for you, whether you want it or not is beside the point, it will add it anyway.

    3. After you have created all your groups just select all your data and then click on the Sort button, sort on, in my example, column A, and the entire data set is sorted by that header/title row, and the 7 rows in each groups are not touched, but stay with the header/title row. Very nice!

    Notice: I did not have to insert any blank rows between groups, this is because I unchecked the two check boxes in the Outline section of the Data tab.

    Also, if you have any merged cells you may get the error box telling you about unequal merged cells. It's best to unmerge any merged cells, and live without them.

    I hope this helps other people who have asked this question in many forums, I see in search results that this is something that has been asked many times on many forums, and there seems to be many ways to deal with it, but why bother with scripts when it's not necessary?

    At any rate, that solved the problem.

    • Marked as answer by ChipWiegand Thursday, August 6, 2015 9:56 PM
    Thursday, August 6, 2015 9:56 PM
  • THANK YOU!!!! The answer I needed. Very clean, very simple. I've been searching for over 4 hours. NO ONE ELSE had anything like this.

    Saturday, April 8, 2017 6:45 PM
  • Unfortunately this doesn't work anymore in Outlook 365.
    Friday, August 16, 2019 11:17 AM