Q: Sorting by Multiple Columns in Numbers 3.0

Thanks for the help SGIII, i think i understand how it works now.

Yes if does work but in the old version it sorted things under sections that i could expand or collapse so i have much better view and controll of things but this have to do for now i guess

Will Send Apple some feedback now as well. Thanks again.

Yes, the old Reorganize panel was pretty convenient, wasn't it.

Not as convenient, but remember you can filter from any cell in a table:

And also do multicolumn filtering:

'

And then sort a filtered view of your data.

SG

Thanks mate, this is great help but sadly its not as good as it use to

Your formula helped allot but i wish the old way it worked existed, one thing i badly miss is how it sorted the items in different rows where i could expand/collapse. Also when i changed a value it automatically moved it to the correct position. Now i need to sort my columns again and again....

Apple sadly made this program crippled when removing so much useful feature so i hope they listen to our feedback.

Update is on the way:  http://support.apple.com/kb/HT6049?viewlocale=en_US&locale=en_US

Good news!

SG

Thank you for the reply - I too have spent many long hours trying to play around with this and figure out why I can not solve what seemed to me, an easy thing to do.

Your very very useful tip about sorting least important first will have to do for now!

Hi mofluk15,

Until multicolumn sorting is reintroduced, you can also use a simple workaround included in this workarounds thread.

Let's say you have columns A, B, and C and you want to sort first by A then by C then by B. Just add a column D with the formula =A&C&B.  Then sort by the new column D.

Ugly (it requires an extra column) but easy to set up and efficient at controlling the sort just the way you want it.

SG

I got tired of the many limitation or lack of feature (even if i'm no where near an expert or use advance stuf) and went ahead and installed Office 2011and WOW what a difference. I simply wont do the misstake going back to the Apple's riddicoulous application. I really wanted to use their stuff but seriously the whole package (numbers, pages, keynote) are far from what office offers. I just get amazed how badly they treat their users and after so much wait come out with this (sorry to say) garbage. Office is by far better in every area.

Apple lost me as a customer even if i'm really loyal towards them and really wanted to use their stuff but this has to end. Also the conversion from pages to word works really bad. I keep changing my documents back and forth so others can see it properly. Its just to much of a hassle.

Any way i just felt to vent here and if Apple even reads these forums i want to let them know there is a limit how much you can push your customers before they abandon your bandwagon and here is where i draw the line!

Hi shadow,

Office 2011 is (I think) a great suite of products. I use Excel all the time. But it's a stretch to say it is "far better in every area."  Have you tried getting it to work well with popular mobile devices?

Anyway, don't you agree these forums are here to help other users, not to vent? This (right in your menu) is a convenient way to communicate with Apple if you feel Apple has treated you unfairly by giving you a free application that you aren't forced to use:

SG

Sorry beforehand if my reply sounds harsh but the reality is applications such as excel should not sacrifice features just to adapt to a tiny screen like mobile phone. Maybe a reader version could be a solution but to cripple the whole application for the sake of mobile phone is to crazy in my world. its like for someone working in CAD the company strip away all the tools and give you a paint application saying well now it looks good in a phone. Ok this is a exaggeration but hope you see my point ?

So basically i'm so much happier having a fully featured MBP application and a limited phone version rather then 2 crippled application that neither are good.

Now using Office 2011 its a huge huge jump in feature and simplicity for me atleast.

I understand your point. But there always are software engineering tradeoffs. Excel is a full-featured application in its element on the desktop and notebook. Like you, I happen to like it. It's engineered as a "truck." It can handle just about anything you throw at it. The new Numbers, though powerful (more spreadsheet functions than the old Numbers and faster), is not designed as a truck. There are things it cannot do. But it is hardly "crippled," any more than a car is "crippled" compared to a truck. A car does not have the power of a truck but it's better suited to many situations.

To extend the transportation metaphor, Numbers now reaches across cyberspace to sync with mobile devices and manages to do that well. That feature can enhance productivity for a lot of people (do data entry or display/sharing on the device and when you get back to the computer everything's all synced up). But when you get on a spaceship, or use a car instead of a truck, or take an airplane instead of a car, you have to leave some baggage behind. The spaceship should get roomier with time, but right now that tradeoff is just a fact of life. It's already a favorable tradeoff for a lot of people.

SG

A collection of comments on vrious topics covered in this discussion. The order doesn't signify any priority; it's just working my way back up the thread.

Numbers vs Excel

The basic rule still applies: Use the software that is best for the job you want tit to do. If Excel is best for that job, then Excel is your logical choice. If Numbers is best for what you want to do, then your logical choice is Numbers. If you're like SG, you'll move back and forth between these (and other applications) depending which has the features that fit the job you're currently trying to do. All are valid strategies.

I'd like this changed in Numbers...

In these discussions, you're talking to users like yourself. Some of these users can and will tell you how you can do something with Numbers. None have the power to change the application itself. Change suggestions should be sent directly to Apple,using the Fedback channel: Numbers (menu) > Provide Numbers Fedback.

Nested sorts:

In Numbers '09 v2.x, this was done in the reorganize panel by setting a series of rules. Each rule chose the column to be sorted on, and the direction of that sort. Not stated was the fact that the sorts were actually carried out starting with the last rule in the list (just as they had been in AppleWorks, to which the iWork applications were touted as 'the successor').

In Numbers 3, the Reorganize panel is missing (so far), but the process is essentially the same. Start with the sort that would have been at the bottom of your list, and work your way to the tp of the list. Here's an example, starting with the original order:

Note that the values in column A can be used to return the table to its original order.

Desired sort:

By name, ascending.

Within the same name, by amount, ascending.

Within the same amount, by payment method, ascending.

Procedure:

Select any cell to bring the focus to the table, then:

Hover the mouse over the column D (Type) reference tab, choose Sort Ascending.

Repeat with column C (Amt)

Repeat with column B (Name)

Result:

All names are sorted in ascending order.

Within each group of the same name, payments are sorted by type.

Where there is more than one payment of th same amount, these are sorted by type (see highlighted examples).

Using an auxiliary sort column:

Because concatenation of the values in columns B, C and D produces a text string, all sorts in this column will be 'alphabetical', not numeric. To correctly sort the values in column C, these must be incorporated as text strings, padded with leading zeroes or another character that will sort before any number character to make each 'number' the same length (measured in characters). That can be done (as SGIII demonstrated) by appending an arbitrary number of zeros to the beginning of the 'number', then taking the rightmost n characters of the resulting string to use in the string being constructed for the sort.

Using an arbitrary number, though requires some skill on the part of the arbitrator. (S)he must add enough characters to accomodate the difference between the longest and the shortest 'number' in the column, and retain enough characters to accomodate the longest 'number' in the column.

SGIII wrote: "If you're doing a lot of this Barry, Jerry or others could probably give you a formula that would look for the largest number value in the column and adjust automatically."

Not all that difficult, but it does require another auxiliary column, this one to determine the length of each number, then determin the length of the longest one.

Here's the table above, with the two added columns.I've also deleted the empty (and hidden) columns in the original table, leaving only those which actually contain data.The yellow filled one shows the length in characters of each entry in column E. This column may be hidden.

Column F shows the concatenated content of columns B, C and D, with C's contents adjusted to the same length in each column. This column needs to be visible in order to be selected as the sort column for the table, but it, and/or its contents, may be reduced in size or otherwise be made less visible.

A single ascending sort on column F gives the same result as the three sorts in the example above:

Costructed and tested in Numbers '09 v2.3. All functions used are available in Numbers 3.

Regards,

Barry

Hi Barry,

Thanks for this clarification. The loss of the Reorganize panel in Numbers 3 is no great loss to me. I have (in Numbers 2 and Excel) found it is quicker and more intuitive to sort one column at a time (least important column first). The Numbers 2 Reorganize panel and the Excel Sort panel require (a non-intuitive) setting up for each multi-column sort. And the setting up is not saved with the document. With an auxiliary sort column, as suggested by SG and clarified by you, the most frequent sort order is saved with the document. If we think that column is ugly, it is easy to hide it. Then we can reveal (unhide) when we add new rows for new data - and fill the sort formula down, if it does not automatically fill down when adding a new row.

Your column A with the original order is a quick way to sort back and "go home" to see the table as it was when we entered the data. Again, we can hide it when it is not needed.

Perhaps one loss to some users moving from Numbers 2 to Numbers 3 is the (now not) ability to sort selected rows.

• If the aim is to sort whilst keeping the column headings at the top, the use of Header Rows will do this. Numbers 2 and 3 have this power, and Excel has not.

• If the aim is really to sort some Body rows within a table, I would question the use of one large table. Perhaps the data should be split into several small tables, each with a common theme.

It seems to me that many reports of what has been lost in Numbers 3 stem from an Excel-centric thinking. Apple has moved my cheese, but the Numbers paradigm has not changed.

Regards to all,

Ian.

A couple of notes on the sort by "a concatenated string in an extra column" workaround.

As Hiroto notes, it has its limitations. It's a quick workaround that works in most situations. And, when used, it's best to "pad" not with leading zeros but with characters that are not likely to otherwise be in the values to be sorted.

Say you want to sort the following first by Category, and then by Payee and then by Cost.

Setting up a column E with the formula  =D&B&C and sorting by E won't give the desired order.  You have to "pad" C, the column with numbers.

The rough and ready way to do that would be to put something like =RIGHT("000000"&C, 4), or, using unlikely-to-occur characters, =RIGHT("||||||"&C,4). Then adjust the "||||||" and 4 until it "works."

It turns out there is a way to "pad" numbers columns without guesswork or setting up yet another column. The padding has to be enough to accommodate the "length" of the largest number in the column being padded, here C.  So the trial 4 in the formula can be replaced with LEN(MAX(C))+1.  And the |||||| can be replaced with REPT("|",LEN(MAX(C)).

Giving this:

   =D&B&RIGHT(REPT("|",LEN(MAX(C)))&C,LEN(MAX(C))+1)

For many this obviously isn't as friendly as using the Reorganize panel or (one hopes) Apple's upcoming implementation of multi-column sort.  But it's reusable and, once set up, easy to apply. Just click the triangle next to the column letter and choose the sort.

SG