How do you sort a row of numbers?

Cartoonguy wrote:

Clever workaround, but doing it messes up the column widths

If you want to preserve the column widths and your table isn't too huge you could duplicate the table (click the bulls-eye to its upper left, command-c to copy, click on the canvas, command-v to paste), then transpose the duplicate table, sort it, transpose it back, select its cells and command-c to copy to the system clipboard, click in the upper left cell of the original table, and Edit > Paste & Match Style.

That all sounds more cumbersome than it is, and would prevent you from having to readjust widths, etc.

SG

Hi 'Guy,

Going back to your original question:

"I have a row (not a column) and the first cell is a name. After that, a bunch of cells have numbers. I want to select all the cells with numbers and sort them in order from lowest to highest."

You also asked "Why is this not obvious?" The answer to that is that something that is missing is not often 'obvious.' What's missing is the ability to sort cells in a row.

You can, however, sort numeric values arranged in a row, using a formula.

The 'numbers' must be actual numbers, though, not things like 'model numbers', which are often mixed alpha-numeric strings of characters.

Heres a method for sorting the numbers in the case you describe:

If you do have "a row", do this:

• click on a cell to activate the table.
• move the mouse pointer to the row reference tab for the row containing the data to be sorted.
• Click on the v that appears beside the reference tab and choose Add row below.

In the new row, click on the cell below the first number, and enter the formula shown (adjusting the cell references to fit the row containing the data, and the number subtracted from COLUMN() to give a result of 1 in this cell).

Click the checkmark to confirm the formula. Then Fill Right to the end of the data.

• With the filled cells still selected, Copy.
• Click on the first 'number' cell of the original data, then go Edit > Paste Formula Results
  This step pastes the copied number, but not the formulas that created/ordered them, replacing the originally recorded data with the same data sorted fro small to large.
• Delete the new row used for calculation (Reference tab.. v ..Delete Row)

If you have multiple rows, it will be quicker to use a new table on which to do the sort. The basic steps are the same:

Original table shown with cells containing data to be sorted in each row:

• Add a new plain table that matches the size if the area whose rows are to be sorted.
  (the one shown was made by copying the selection above, then clicking on an empty space in the Sheet and pasting.
  The data shown will be overwritten by the formula's results)
• Enter the formula in the top left cell of the new table.
  =SMALL(Table 3::$F2:$L2,COLUMN())

• Note that this setup needs no adjustment of the value returned by COLUMN().
• Click the checkmark to confirm the formula.
• Fill the formula down to the bottom row of the new table; Fill right to the last column.
• With the results still selected, Copy, then click on the top left cell of the original table.

• Go Edit > Paste Formula Results
• Select the calculation table. Press Delete.
• Done.

Regards,

Barry

I think all spreadsheet app are designed to sort columns, not rows. But it is quite easy to accomplish what you want. With the table selected, choose Table > Transpose Rows and Columns from your menu, sort the by the numbers in the column, then choose Table > Transpose Rows and Columns again.

SG

Cartoonguy wrote:

why, when I select the specific column I want to sort, it then reorders the other columns as well? Shouldn't it only sort the one I choose in the drop down menu?

Actually most spreadsheet users expect all other "columns" to follow the column being sorted. It may help to think of each row as a record in a database where all the pieces of information belong together.

However, if you want to sort just one column, or some cells in once column, Numbers again makes that pretty easy. Select the cells you want to sort, and with the mouse button down drag those cells onto the canvas (it may take a little practice to get them to "lift up" so they can be dragged). Once they're in a separate table on the canvas, sort as desired. Then select the cells in that resorted table and drag them back into the original position in the original table. Again, much quicker to do than the description suggests.

before I choose to add a column to sort, the default option says, "sort selected rows". That's what led me to believe that I could sort a row. Why does it say that if it doesn't even do it? "Sort Now" remains greyed out.

That's for sorting only part of a table, rather than the whole table. Select some rows and that menu pick should no longer be greyed out. Sort rows here means put entire rows in order, rather than put the values in different columns in each row in order.

SG

Hi all,

The answer, I think, is that both Numbers and Excel can quite easily sort a single column. You just have to know how. In Numbers, it can be done as described up-thread.

In Excel, IF you do not have your data organized in an Excel Table, then you see something like this when you try to sort cells in one column only:

If you 'Continue with the current selection' then only those cells sort without affecting the others. The default, however, is to expand the selection, because presumably that's what most users want.

If you have your data in an Excel Table then the behavior is similar to Numbers.

The "whole table" (i.e. all the columns) sort. If for some reason you really do want to sort only cells in one column, you do something similar to Numbers. You take the values outside the table, sort, and reinsert.

The bottom line is that you can do what you want in either app without much trouble; you just have to know how.

Both apps assume (correctly I think) that in most cases a user will want cells in all columns to sort together.

SG

Hi 'guy,

'Imagine you are working on a text document and you want to make one word in italics and you are told that if you do that, the entire document will be in italics, so you have to move the word to another document, make it italics and then copy it back. How dumb would that be? The notion that I can't select a column and sort it without the others also sorting is clear to me, but baffling.'

Formatting is not rearranging. Italics is a format applied to characters. What you describe here for a text document can be done in a Numbers spreadsheet Table as well. Note "price" and "length".

Now if you wanted to sort the third word in each sentence (or paragraph) of that text document, that would be a closer corollary to sorting the contents of column C of a spreadsheet table. I suspect there are not many text processing or word processing applications that would let you do that without having to jump through several hoops (if they'd provide for you doing it at all).

'It just seems totally arcane to have to move data out of the document just to sort it.'

Well, it would be a bit more trouble if you did have to do that, but you don't. the single column has to be in a table separate from the part of the table that you do not want to be included in the sort, but it can be in the same document; even on the same sheet.

Cheers,

Barry

Fuzzy language, this English. 😕

This may be clearer: "the single column has to be in a table separate from the part of the table that you do not want to be included in the sort, but it (the single column table) can be in the same document; even on the same sheet."

Try this sequence. Times in parentheses are from the time stamp of each screen shot:

Original Table (7:09:25 PM)

Cells C2=C16 copied, then pasted onto (a different part of) the sheet, away from the original table. (7:10:06 PM)

Cells of Table 3.1 (the one column sorting table) selected, sorted ascending, then copied.

Cell C2 of original table selected, Edit > Paste and Match Style. (7:10:49)

Select and delete the sorting table (one click, press delete).

Total time: 84 seconds (including the time to setup and take the second and third screen shots—about 20-30 seconds).

"How can I select one column and sort it only? And I would really prefer to avoid the hassle of copying and pasting into another document to do so."

Already answered above. Numbers sorts a Table using the data in one or more columns.

If you want to sort the data is a single column, you need to remove it from the existing multi column table so that a) it is in a single column table, or b) it is in a multi column column table in which there is no other column with data, or there is data in other columns, but there is no concern over that other data being rearranged.

Once removed from the original column, the data can be sorted, then returned to the original table without disturbing the order of other data in that table.

You can 'remove the data' for sorting in several ways:

• Select its column, then click to the left of the column index letters in the column's reference tab, and drag the whole column out of the table. Drop it on the Sheet. Sort, then drag the column back to its original position in the original table, and drop it when the space you want it to go opens behind it.
• Select the cells containing the data to be sorted Copy. Click on an open space in the sheet. Paste. Sort the resulting single column. Select. Copy. Click the first cell of the original column (once) to select it. Paste.
• Use a script* Select the column to be sorted. Choose the script in the Scripts menu. When script finishes, Paste. (or you may be done, depending on the script).

You could also maintain a continuous and automatic sort, using data entry in a separate column/table, and a formula in the 'sorted' column using either SMALL() for an ascending sort, or LARGE() for a descending sort, automatically calculated after each number is entered in the entry column.

*Using a script, of course requires either writing one or using one written by someone and simply used by you. SGIII has been the most frequent provider of short scripts for straightforward tasks like this, some wrapped as Automator Services. There are a few others who contribute scripts to the discussions here as well.

Regards,

Barry

"Still seems bizarre that spreadsheet designers assume no one wants to sort a row, like I do. They need to get out more!"

It's a design choice.

Spreadsheet designers go for the big picture, and decided long ago that a database table was the model to use.

Each row is a record—a group of data in a set of fields that should stay together.

Each column is a field.

The standard sort is to sort the records on the data contained in a single field, or on a hierarchical list of fields.

Apple's designers, working on AppleWorks 6 made mostly cosmetic changes, but they did make one significant design change: They added the capability you want—to sort a single column within a spreadsheet table.

I was in charge of judging the exhibits at the regional science fair for several years, and a colleague had developed a spreadsheet in either ClarisWorks or AppleWorks 5 to do the calculations on the scores submitted by the judges. We'd been using it for several years, making small improvements each year. When AppleWorks 6 was released, the schools upgraded, and the science fair people (mostly teachers in one of the districts in the region) adopted it if it had been installed in their schools.

All was well until, deep into recording the registrations for the fair, it came time to sort the list.

We expected a standard "sort the table on the data in the selected column' sort, but got a sort of a single column.

Several hours later (and some hours into the next day, which was a school day) we finally had everything moved back into the record/row it belonged in, and made the decision that for that year at least, we'd save the file as an AppleWorks 5 file, load it into AW 5, and finish the fair with that application, for this year at least.

When Apple eventually declared EOL on AppleWorks, and delivered Numbers '08, the developers had apparently learned their lesson—a sort sorted rows with the sort keyed on data in one or more columns. Data stayed in the same row as the rest of the data associated with it, and the whole row moved together.

For those who needed to sort single columns without disturbing the order of the other columns, a simple solution soon arose: Take the data (and the column itself, if you desired), perform the sort on that single column table, then return the sorted data, or the column containing the sorted data to its original location within the original column. Quick, easy, and with the conscious effort needed to separate the data from the rest of the table, much less chance of the misfortune we suffered on that occasion several years ago.

Regards,

Barry

Hi Barry,

Another spreadsheet horror story. A colleague working on a large Excel document asked me how to sort a sheet. "Do you select the column by which you want to sort the whole sheet?"

I replied: "No. that sorts only that column." The look on his face told me he had already done that. Years of work and thousands of research dollars down the drain. I panicked until we found we had a backup copy. All's well that ends well.

So yes, Excel can sort a single column (or it could at that time).

Regards,

Ian.