Q: Sorting by Multiple Columns in Numbers 3.0

Hi Saudra,

Spreadsheets aren't easy for most people. Here you can do something like this:

To your imported table you add an extra column:

Convert your first row into a Header Row (that's important in Numbers).

Add your formula into row 2 (first row that is not a Header Row) of the column you just added (I've named it Sort).

"Fill" that formula down through the rest of the rows of the new column by dragging the yellow handle.

Sort on the new column:

And you've got your sorted table:

To change the order by which you select columns for the sort just adjust the formula in D2 and fill down again by dragging the yellow handle.

It would be much nicer to have this built in the way it was, but this is not that complicated once you've done it once or twice.

SG

Olathe Mike,

Everything is still in tact in Numbers 3.0  and all is well.  See Examples below.  Click one time in the column you want to sort and see the headers of each column, i.e., A, B, C.  Now put your cursor over one of the Headers and click on the ▾ in the Header and a dialog will open that will allow you to do multiple manipulations to the column, i.e., Sort Asending, Sort Decending, etc.  Next drag the selection box though the columns and rows you want to sort then select how you want to manipulate them.  As you can see in my examples, I sorted the Date from 1 to 5.

Hi Joe,

I've tried to follow along here, and it seems you have done a single column sort, by Date.  Have you figured out a way to do multi-column sort, in other words first sort by Date, and then within date by Cost or by Payee? Or by Payee then within Payee by Amount? It's the multi-column part of the sort that Numbers 3.0 doesn't seem to have in its interface any more, making the workarounds above necessary if you really need that.

SG

SG,

I'm not sure I follow you but, if I do, this is what I've always done and it works in Numbers 3.  If I want the Cost or Payee sorted as well as the Date, I sort the Cost or Payee column first by Ascending or Descending order and then Sort the Date by Ascending or Descending order.  Once Sorted by Date, either the Cost or the Payee remain in the correct order as determined by sorting them first.  The overall sorting takes two separate sorts.

Hi Joe,

So I start with this (note that I've changed your example to have multiple payments to Payee 4 and 6):

Then I decide I wanted to have a list sorted first by payee and then within payee by cost, so that the $20 payment to Payee 4 appears before the $40 payment, and similar for Payee 6.  If I understand you correctly, you're saying I can just sort first by payee:

And then by cost:

But that gives this (the Payees are out of order again):

A multicolumn sort (using the extra index column workaround upthread) would give this:

The payees are in the desired order and the smallest payment for each payee is listed first.

Have you found a way to do that multi-column sort right in the interface of Numbers 3.0?

SG

SG,

I am getting the same results as you but mine is without the extra column D Sortindex.  My result is below.  I walked through your example and ended with what you see below.  I started with the same arragement that you started with above.  I'm not sure what I'm missing and it seems to work for me.

Joe,

Youre the only one getting this result. I have tried it also, and get the same results as SG.

Joe,

Actually I'm not sure what I'm missing here. I think I saw Jerry describe being able to do it straight in the interface too, and I was puzzled by that. Do you mind describing exactly what steps you use? Up-thread you describe "dragging the selection box through the columns and rows."  I couldn't figure out what you mean by that, and maybe that's key. You seem to have some way to "freeze" the already sorted columns and go on to sort the second column. But I can't reproduce it here.  If it's possible, this would be a big help to everyone. So more details would be very helpful.

SG

Thanks mf, Joe, and Saqib,

I think I see what to do. I don't think you need to hover or drag the selection box or do anything fancy.

If you want to sort by Payee and then within Payee by Cost, you first do the usual single-column sort on the Cost column, then do the usual single-column sort on the Payee column.  In other words, first sort on the second column you're sorting then sort on the first.  I was doing it the other way around.

This can work with some non-numerical columns too.

If you've got a three-column sort then you're better off going with a special "sorting" index column until they add this functionality back into the interface.  Agree that having a special sort column isn't exactly modern.  But it's not hard and it gets the job done reliably for now.

Numbers is the only game in town if you ever want to take spreadsheets with you on a tablet (the latest version already works far better than I could have imagined) unless, of course, you want to buy a tablet with a kickstand.

SG

SG and others,

Sorry for leading you astray about draging the selection though the columns and rows. It was done that way in Numbers 2.3, i.e., you could select the rows and columns you wanted to sort though.  Now it seems it does a sort of the entire table.  I liked being able to select the rows I wanted to sort but Numbers 3 works as well for me. When I was making excamples for SG I was not dragging though the table but rather clicking on each column.  I forgot that I had said that in the beginning.  My apologies.

I'm glad you are seeing results now.  Numbers 3 is working fine for me and I'm sure there are improvements that can be made and that will be made in future releases of the app.  Feedback is useful to Apple.  Use the Provide Numbers Feedback in the Numbers Menu for Apple to see what changes or inclusions or exclusions you desire.

Thanks, Joe. No need at all to apologize. I'm really glad you posted because otherwise we wouldn't all have taken a closer look and realized two-column sort does work for many situations. For me that got lost in all the fuss about the loss of the Reorganize panel. Agree with you that, while Numbers 3.0 isn't perfect, it sure seems to have a lot of promise if we give feedback and Apple keeps developing it (since it's now free, for them it's a loss leader designed to keep us from leaving the Apple ecosphere and giving people a reason to buy more of their gadgets). For me going back is not a good option.

SG

Thanks so much for the detailed reply...I really appreciate it. Now that I understand that the formula has to go in each and every row, it isn't all that complicated, especially using the fill down function. I'm also glad to learn how important the header row is; I'll make sure I do that in future spreadsheets.

Waoo that actually worked but still a hassle, before when i changed one row it moved to the right place, now i have to sort multiple times to get the same result :/

One question SGIII, in your example for =A&RIGHT("0000"&C,4)&B where C is digit are you assuming that there is a max number of 4 digits and the "0000" is somehow mixed with whats in column C so that they are treated as characters? Also i guess the digit 4 is just for the number of zeroes ?

Sorry just want to understand this since i might be needing to use digits later, now i'm using 3 column with text.

Thanks allot for sharing this info.

Hi shadow,

Glad it helped. That's a good question about the number of zeros needed in front and the number in the second argument of RIGHT.

I just gave =A&RIGHT("0000"&C,4)&B as a quick example of what kind of approach can work for people who have do a lot of multicolumn sorting.

If you have larger numbers you'll need to adjust that, as I did in the example a few posts up in this thread, where I had:  =B&RIGHT("0000000"&C,9)

Say the original value in the cell of a column you want to sort is $233.91. All the last part of this formula does is turn $233.91 from a number into a "string" and add ('concatenate") seven zeros in front, giving 0000000$233.91. Then it takes the right 9 characters of that, giving 00$233.91. If you've got larger numbers in your column you need to add more leading zeros for "padding" and increase the second argument in the RIGHT() function. 

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. I tend to just put in more padding than I think I will need and then add more if the column doesn't sort correctly.

Hope the next release includes multicolumn sorting to match the multicolumn filtering they already have so we don't have to resort to a sorting index column. It's a bit ugly.  But it's not that hard, and it does work reliably.

SG