Q: Sorting by Multiple Columns in Numbers 3.0

Hi SG,

Thanks for that enhancement. I'd thought of building the length detection into the concatenation formula but did not have the time to continue the investigation on Friday (or Saturday, for that matter). Happy to see it was indeed possible.

Regrards,

Barry

PS: I was surprised to see that a vertical line (pipe) character (Unicode U+007C) sorts 'alphabetically' before any of the numerals (Unicode+0030 to +0039).

B

Hello Barry,

Regarding the PS, the sorting order of Unicode characters is fully defined in Unicode Standard. The order is not the code point order.

cf.

Unicode Technical Standard #10, Unicode Collation Algorithm

http://www.unicode.org/reports/tr10/

Default Unicode Collation Element Table (DUCET)

http://www.unicode.org/Public/UCA/latest/allkeys.txt

Regards,

H

I get what you are saying but still people are not going to work efficiently/optimal with a heavy application like excel on their phone or even ipads i think. its sort of crazy that some simple operation need this much hassle. Currently when i use excel or even Word all the features i need are easily accessible but in the new numbers and pages its like playing cat and mouse. Features i came to rely on are gone. Its hard for me to go back to using these programs at this point since i feel cheated and already lost tons of time going back and forth so now i will be sticking to Office package. No offence SGIII you seem like a smart guy and very helpful and i do see what you mean but i dont like the way Apple just runs over the people and neglect peoples current need. They could have waited and added all the features they removed until they had a ready product instead of pushing out a "crippled" version. You might not agree with me but as a user when i dont find the features i rely on that is how it feels for me. i'm not a advance user like many of you guys so doing so many trix to get the "simple" things done is just not what i like to do.

I dont want to keep complaining here so good luck to you folkz.

ps. I know Apple dont read here so i already send them my feedback.

people are not going to work efficiently/optimal with a heavy application like excel on their phone or even ipads i think.

That may be the crux of the problem.  Apple has opted for an application that can work well on mobile as well as notebook/desktop.  Excel is a fine product, I think, but isn't suited for mobile (at least not yet). 

Anyway, multi-column sort will be reintroduced, we are told. It will be interesting to see how Apple continues to develop Numbers 3 and Numbers for iOS as the power of mobile devices grows.  On this, to me it seems they are a step ahead of Microsoft. We'll see.

SG

I have 26 colums headed by text titles I have entered randomly as they have come up.  I now want to arrange the colums in alphabetical order so it is easier for me to input the data below the titles.  For the life of me I cannot find a way to do this or, do not understand the instructions well enough to make it happen.  I can do the job manually but why should I if this thing works the way it should?

I think you're sorting columns, not rows, and that you need to do this manually. It's pretty easy in Numbers to click on the column letter, hold down the mouse button and drag a little until the column "lifts" up, and then drag to where you want it between two other columns.

SG

Yes it can be done manually but why?  I can do the math manually too but I expect the program to do it.  If Numbers can sort rows why not columns?  Numbers is new to me and so far it is not very satisfying.  It should improve on its competition not concede simple elements and expect users to compensate with "manual" tools users expect the program to replace.

Numbers and other spreadsheets are good at sorting rows. If you have data that often needs sorting and resorting then transpose it so you can sort the rows.

It is possible to extract a table from another that has its columns resorted but that is probably not worth the candle.

If you have a specific problem you are trying to address in Numbers perhaps you could post more details.

To use a tool effectively, and thus find its use satisfying,  it helps to spend time understanding how it works and what it's good at.

SG

"If Numbers can sort rows why not columns?"

For the same reason that it does not sort individual coumns. Such a sort would not fit the model which Numbers is designed to follow.

Data in a numbers table is treated as a set of records. Each record consists of all the data in a single row of the table. The table can be sorted, but the sort cannot 'break' any of the records. Rows move as a unit.

Each column in a row represents a single field in that rows record. Fields may be rearranged (ie. columns may be moved), but the model assumes this is something that will be done in the course of setting up the table, will not be a continuing procedure, and will not usually require the fields to be in ascending or descending order using any simple sorting rule.

Design your table(s) with that model in mind, and thigs will go well. Work against the model, and you'll be frequently frustrated.

Should you require a different model, you have a couple of possible courses of action: Use an application that offers support for the model you require, OR convince Apple that the model they have chosen is too restrictive, and that they should change Numbers to fit your needs.

If the second option is your choice, then Provide Numbers Feedback to Apple, using the tem of that name in the Numbers menu in Numbers.

Regards,

Barry

ClarisWorks and AppleWorks could do both horizontal and vertical sorting. In such a limited environment as Numbers where only vertical sorting is implemented, you'd need to transpose the table first, sort it by desired row which was the desired column and then transpose it back. And yet, transposing in Numbers is very tedious and even impossible (without using dedicated script) when cells have formulae with inter-references in the transposing region.

Too bad,

H

Transposing a table is probably most easily accomplished as Barry describes here.

You need to set up the same number of rows in Transposed as you had columns in Original, and same number of columns as you had rows.

For the example below the formula in A1 of Transposed, copied over and down, would be:

    =OFFSET(Original::$A$1,COLUMN()−1,ROW()−1)

You can also transpose this way:

    =INDEX(TRANSPOSE(Original::$A$1:$E$6),ROW(),COLUMN())

Then select the transposed table, copy (command-c), Edit > Paste Formula Results and you can sort on rows as needed.

SG