Sort by group

[nnoop]

is it possible to sort stuff by Groups i cant find an option to do so, also is it possible that the group "-","+" is on the topside and not at the bottom.
Sort by group.PNG
Excel 2016

[Richard Buttrey]

Hi and welcome to the forum

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context. Pictures are rarely much help since no one wants to re-create something you already have.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

One possible solution is to hold a helper column which contains a reference to a Group and a numeric. e.g. A1, A2, A3, B1, B2...etc and sort on that but when we see the result you expect from a gibe starting point we can advise further.

[nnoop]

Before i closed it i could at least sort it but now sorting doesnt work at all when i sorted it by column C it would just sort everything even inside groups so numbers were on the bottom of the sheet while the Words are on the topside. now after safing and closing it i cant sort it any longer.

i added a helper column based on your suggestion, i didnt do anyhting else with the helper row just make it.

[Richard Buttrey]

It's still not clear what you expect to see AFTER a sort has taken place. As requested in #2 please manually add the result you want. We need to see the resulting sorted order

When I mentioned a helper column I meant that each cell in the column would contain a reference.

To have the +/- at the top of the group from the ribbon choose Data....Outline select the little down/right arrow to bring up the options and untick the first one.

[nnoop]

sorted these things by A-Z but the values under the object beeing still under the same name. sort by group as directly seen or hopefully understandable what is inside a group should remain but the placement are not in the right order A-Z

not sure if i understood the reference thing but i cant just put letters and numbers down the line?

also thanks now the groups expand downwards what is the reason it is normaly expanding upwards?

[Richard Buttrey]

I've still no idea whether the layout you show is after you've sorted a previous layout, or whether this is what you start with and you want some different layout.

The default group outline is to show the symbol at the bottom. Why Microsoft decided it would be at the bottom rather than the top I've no idea. You'd have to ask them.

The helper column formula I was thinking of was something like

Please Login or Register  to view this content.

in A3 and copied down. That would give you a primary sort key and another column could be a secondary key.

[nnoop]

there is the column with the name "mask" and the names i consider a group mask 1 mask 2 mask 3 but they are not sorted by any means and copy pasting a group around takes ages

((Yaahl Gear (mask 1)
+ 10% Weapon Handling
+ 8% Hazard Protection
+ 5% Weapon Handling) Group 1)


((China Light Indsutries (mask 2)
+ 10% Explosive Damage
+ 10% Shotgun damage
+ 10% Cooldwon Reduction) Group 2)

not sure how much more i can show how its supposed to be a group

also what is that formula? it doesnt do anything or i am just not understanding it.

[avk]

If you manually enter in your file before data & after result data, its more clear idea.

[nnoop]

not sure if that sheet shows what i meant but if it isnt understandable this way i dont know

[Richard Buttrey]

OK, let's try this.

First remove the Grouping
Now in helper column cell A2 copied down

Please Login or Register  to view this content.

Now sort columns A:C using column A as the sort key.

The formula creates a number consisting of the ASCII code value of the first character in the group heading plus the ASCII code value of the 2nd character in the group heading divided by 10.

If it's possible that two or more group headers might contain the same two character then add another element to the formula to get the 3rd character and divide that by 100

e.g.

Please Login or Register  to view this content.

[nnoop]

where do i put the formula to use in excel exactly? under sort?

[Richard Buttrey]

Follow the steps I gave you.

I don't recognise your query about 'under sort'. Sort is a Ribbon menu item which allows you to sort whatever range you subsequently choose.

After you've Ungrouped your data put that formula in cell A2 and copy it down your list of data. Then select A2:Cnn where nn is the last row of data and click the SORT menu item, and select column A as the sort key and smallest to largest as the sort direction.

[nnoop]

ok i ungrouped everything i put in =IFERROR(IF(C2="",CODE(LEFT(C3,1))+VALUE(CODE(MID(C3,2,1))/10),A2),999999)
into A2 and everything below however i tryed to sort it, so i choose row A (Helper) i sorted it from A-Z but it didnt work.

Has Cnn anything to do with row C? do i need to mark row A and C?

[nnoop]

ok i found a solution it might not be the most optimal but it does the job
i used 2 formulas, for the name

Please Login or Register  to view this content.

for the values or the next 3 rows

Please Login or Register  to view this content.

in the Line i have set Bonus depending on how many pieces are involved there is a number 1-9