You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

how to sort an entire table (numerically) on numbers

Am I the only one having trouble sorting an entire table on numbers?

I need to sort all of these cells into numerical order, when I select everything and try to select sort entire table NOTHING HAPPENS. What's going on it's not this hard on excel, does numbers not have that function? Every time I try to google it nothing comes up lol what am I doing wrong??

Posted on Sep 30, 2020 2:18 AM

Reply
Question marked as Top-ranking reply

Posted on Sep 30, 2020 5:42 AM

If you're trying to sort the numbers in all the columns into one list, then you can do something like this:




In B2 of the second table, filled or copied down the column:


=SMALL(Table 1::$A$2:$D$8,ROW()−1)



If you need it largest to smallest, then:


=LARGE(Table 1::$A$2:$D$8,ROW()−1)


Once you've got the list you want, you can then "remove" the formulas by selecting the column, command-c, to copy, followed by Edit > Paste Formula Results.


Substitute ; for , in the formula if your region uses , as a decimal separator.


SG

6 replies
Question marked as Top-ranking reply

Sep 30, 2020 5:42 AM in response to Jordannnnk

If you're trying to sort the numbers in all the columns into one list, then you can do something like this:




In B2 of the second table, filled or copied down the column:


=SMALL(Table 1::$A$2:$D$8,ROW()−1)



If you need it largest to smallest, then:


=LARGE(Table 1::$A$2:$D$8,ROW()−1)


Once you've got the list you want, you can then "remove" the formulas by selecting the column, command-c, to copy, followed by Edit > Paste Formula Results.


Substitute ; for , in the formula if your region uses , as a decimal separator.


SG

Oct 1, 2020 1:48 AM in response to Jordannnnk

"What does it mean when it says sort entire table?"


That would depend on what "it" is.


If "it" in this case is a Numbers table, "sort entire table" means "sort all rows of the table based on the values in one or more columns."


A Numbers table is built on a 'data base' model in which each rows is a single record and each column is a field within the records.


Sorting, in this model, is designed to sort the records and to keep each record intact.


IF the sort is based on a single column, then the rows of the table will be sorted according to the values in that column. If you choose to do a sequential sort on the values in two columns, the table will be sorted according to the values n the first column, then, where there are rows with the same value in that column, the rows will be sorted within those groups according to the values in the same rows of the second column.


Examples:

Here is a table containing random numbers between 1 and 25. The original table is shown on the left.

On the right is the same table, sorted ascending on the values in column A.

Note the yellow highlighted row contains the same set of values in both the unsorted table and in the table sorted on the values in column A. It also remains in the middle of the three rows containing 14 in column A.


In the example below we see the single sort table on the left, and the same original table after being subjected to the two step sort defined in the panel to the right. As there is only a single set of more than one cell with the same value in column A, these three rows will be the only ons affected by the second sort in column B.



With your model, where each cell contains an independent datum, the sort feature will work only if each value is in the same column and in a different row.


To get the sort pattern you want, you'll need to turn to a formula as suggested by SGIII.


SG's formula is set to put all the values into a single column. The variation below uses a slightly more complex formula, but puts the list into a table of the same size and shape as the original.


The formula below the table is entered in cell A2 of Table 2 (on the right), then filled right to the last column and down to the last row.


A2: SMALL(Table 1::$A$2:$J$11,(COLUMN()−1)×ROWS(A,1)+ROW()−1)


Both tables must contain the same number of rows and columns. The formula assumes both tables will also contain one header row, and no header columns.


If you get a syntax error message, I would suggest you enter only the part of the formula shown below in bold and fill that across and down. The bold part should return the ordered numbers from 1 to the number of data cells in the table.


SMALL(Table 1::$A$2:$J$11,(COLUMN()−1)×ROWS(A,1)+ROW()−1)


When that is cleared of any errors, ad the normal weight parts before and after to complete the formula as shown above.


Regards,

Barry

Sep 30, 2020 4:36 AM in response to Jordannnnk

You can right click on a column letter and sort by that column ascending or descending without having to set up a sort in the sidebar like you are trying to do.


If you want to set up a sort based on more than one column, you do it like in your screenshot but you have to "add a column", just like it says in your screenshot. You have to tell it what column(s) to sort by.


If, however, you are expecting your table of several columns and rows to sort into numerical order with the lowest number from all those cells ending up in the top left and the highest number ending up in the bottom right, that is not how sorting works.

Sep 30, 2020 10:24 PM in response to Badunit

What does it mean when it says sort entire table? I am just doing this for my stats class and we have to sort the table in numerical order it seems so much easier to just sort it with the lowest being in the upper left cell rather than moving all of the cells into 1 column or row. Someone showed me a formula to do it on google sheets and excel.

Sep 30, 2020 11:04 PM in response to Jordannnnk

For the 'ranking' parameter you can use ROW()-1 as in my example.


=SMALL(Table 1::$A$2:$D$8,ROW()−1)


Or in your new example:


=SMALL(Table 1::$B$2:$E$8,ROW()−1)


Be sure to Preserve Row and Preserve Column so you have the $ anchors as shown in my example.


The ROW()-1 returns the row the formula is on, less 1. So for cell F2 it would return 1, telling SMALL you want the smallest number, for cell F2 it would return 2, telling SMALL you want the second smallest number, etc.


SG

how to sort an entire table (numerically) on numbers

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.