sort grouped rows in excel

Question

i have such a strange case! 

i have a table with grouped rows.

normally i sort, and each group remains together as one block.

suddenly today, the sort action, sorts all of the rows as if they are not grouped.

so all the groups are broken and won't stay together anymore.

this started today, and moreover - this is happening only in one of the tabs, while the other tabs continue to work well.

when i copy the good tab to another tab or new excel file, it still works well with sort.

when i write a new table in a new tab or even new sheet, it won't sort well anymore.

but i didn't do anything different that may explain the difference.

so in other words, starting today - for a certain tab, and for new tabs, excel stopped considering the grouped rows when i sort. and for other tabs it still continue to consider it well.

isn't that strange or what?

Answer 1

If you want to group rows in a sort you have to use a helper column which defines which row belongs to which group.

Then you can start a sort and the first level is to sort by the Group, then by the Value to get the desired result.

Andreas.

Answer 2

Well, I'm looking for a solution without adding columns. 

As I said, in some tabs within the same worksheet are working just fine, the excel keep the rows grouped after sorting. Meaning the capability exist. 

Moreover, the same tabs are working well on one computer, and messed up in other. 

Moreover, when I try to create a new test version in a new tab or worksheet, it sometimes works well and suddenly goes wrong, and vice versa. 

And I could not find any reasonable explanation for this.

Answer 3

As I said, in some tabs within the same worksheet are working just fine, the excel keep the rows grouped after sorting. Meaning the capability exist. 

And I could not find any reasonable explanation for this. 

Show me a file with 2 sheets, one sheet that works as expected and one that doesn't work.

And tell me how you sort each and what result you expect.

Share OneDrive files and folders - Office Support

Andreas.

Answer 4

it's very hard to paste here all the variations. (before/ after / compare to trial tab - before and after). it's much more clear if you see it in action.

i prefer to share my screen and show you. 

regarding to how i sort, you know, the simple way, the small triangles on the top column. but it doesn't matter really how i sort ! :-)

why ? because i do it the same, in different tabs or sheets or computers, and get different behavior. it works perfect in some cases, and fail in others. moreover it's changing between computers, even the same tab of same file.

believe me i tried numerous times, in numerous ways.

the main point is, excel does have this capability.

so i think it's simply a bug. 

because normally if there is a conflict, it will pop up a warning note, "this operation is not possible", asking to change the selected range /formula/ etc.

i'm scheduled to a phone call from microsoft support today. let's see what they will say...

Answer 5

update:

after conversation with Microsoft support, my conclusion is - this issue is not considered on the excel program development level.

i wish the development engineers will consider it in their next excel version.

they are not in direct connection with support department, so i have only left to wish.