Sorting selected cells

Hi suisse2k,

You wrote:

"If I select the cells and drag outside of my table, it doesn't create a new one.

What am I missing?"

Hard to tell. Here's another shot of the table in my example above. The first single column table is the one created by the process in my response:

• Select the cells t be sorted.
• Copy
• Click on an empty spot on the Sheet
• Paste

That table's original order was 7 at to top to 16 at the bottom.

It was sorted descending, resulting in the order seen.

Then the cells were re-selected, copied and Pasted into the larger table by selecting the cell then containing 7 (the first empty cell in this post) and pasting.

The second single column table was created today, using Badunit's described process. It is the same set of numbers as I used yesterday, in the order they were after sorting then.

• Select the cells containing the numbers 16 to 7 (top to bottom)
• Click and hold on any cell in the selection, and drag right until the pointer is in an empty space on the Sheet, then
• release the mouse button to drop the selection onto the Sheet, were it formed the rightmost single column table.

Either process should work.

Badunit's is faster if you have a steady hand, and can avoid accidentally dropping the selection on the table, where it could replace the values in several cells; mine is slower, but may be less prone to that type of accident as you have to deliberately Copy and Paste the data to form the single column table and to replace the sorted numbers back in the original table in the new order.

Here's the end result after resorting the small table to the original order (ascending) then continuing with Badunit's suggested process to finish the task:

• After sorting ascending, select all cells in the single column table again.
• Click and hold on the top cell (now containing 7) and dragleft until the pointer is on the top empty cell in column A, where the numbers were before the process started.
• with the pointer on that cell, release the mouse button to drop the selected numbers back into the empty cells.
• Delete the now empty) single column table.

Regards,

Barry

Hi suisse2k

Actually restricting sorting of tables to sorting the rows is even more basic than providing built iin options to do other kinds of sorts.

Excel has these option partially because of its worksheet model using a vast sea of cells containing islands of data.

Numbers uses a different model: a 'blank canvas' sheet on which one or many (usually smaller) Tables may be placed, and which may be independent or may work in partnership with other Tables on that sheet or on other sheets.

Each table follows a database model with respect to its data:

Each ROW of cells is a single RECORD.

Each COLUMN contains a single Field of all of the records.

The Table may be sorted using the content of a single column or of multiple columns with an ordered sequence of sorts.

None of those sorts are allowed to change the content of any RECORD.

To sort a single column, or part of a single column, you'll need to place the values to be sorted onto a separate table, sort that table, then return the values to the original table, replacing the pre-sort version of those values.

Example:

Here's a five column table currently filled with

• an alphabet, listed in ascending order in column A
• a list of integers in ascending numerical order
• the same integers listed in descending numerical order
• an empty column
• a column of random values between 0 and 1, determined by the RAND function.

The order of the rows in the table is easily reversed by sorting descending on columns A or B or by sorting Ascending on column C:

Result:

Or you can sort the rows of the table in random order by doing either type of sort on column E.

TO sort ONLY a section of the numbers in one column you need to:

• select the numbers you want to sort
• Copy.
• Click on an empty part of the Sheet.
• Paste.

The paste will create a new single column table containing the copied numbers.

• Sort the column, then select and copy the numbers in their new order.
• Select the cells the numbers were taken from in the original table.
• Paste

• Delete the single column Table (or keep it for the next time you need it).

Regrds,

Barry

That is the case. In Numbers all columns are included in the sort, you cannot exclude any columns. This is because Numbers considers a row to be a "record" that must all stay together. I hate to think how many times Excel users have screwed up their spreadsheets/databases by sorting only one column when they meant to include them all (which is why it always asks if you want to "expand the selection").

Assuming the columns you want included in the sort are contiguous (like A-E), you can lift them out of the table to create a new table from them, sort them, then put them back into the original table. It is kind of a clunky workaround but it gets the job done.

1. Select the column(s) you want to sort. You must select the entire column(s). I usually click on the first column letter then shift click on the last to select them.
2. Click and hold on one of the column letters and the selected columns will lift out of the table
3. Drag them to an empty spot on the sheet and drop them to create a new table from them
4. Sort that table
5. Add one new column to that table
6. Lift the columns out of the table (except for the one you just added) and drop them in place in the original table
7. Delete that one extra column/table.

I recall in the past we did not have to add then delete that extra column. More recently, though, it seems we cannot lift, drag, and drop an entire table into another, you always need at least one column to remain. Might just be my faulty memory or I am overlooking something.

Sort of selected cells in a column is actually just about as easy as Excel.

Say in this table you want to sort B3:B5 in descending order.

Just select B3:B5, hold down the "mouse" button and drag to the canvas, forming a new table automatically.

Sort Descending:

Select the cells in the newly sorted table, and drag them back into place in the original table.

Done! In a few seconds.

SG

Select the entire column by clicking once on the column letter then click and hold on the column letter. The entire column should "lift" from the table and you can drop it somewhere else. If you drop it on a blank spot, it will create a new table.

This drag and drop of columns is most useful, though, for rearranging columns in a table. You can select the column and drag it to another spot in the same table.

You can drag and drop cells also, like Barry showed. This will move those cells to the new spot and the old cells will be cleared.

I am a newly committed convert to Numbers since Excel has now gone to a cloud based subscription. Much of my (mild) frustration with Numbers stems more from things not being where I am used to them being in Excel or with things being done slightly differently in Numbers. So far though, I've found that Numbers will do what I need if I can just figure out how (or garner the assistance of the community). 

However, the lack of selected cell sorting functionality  is a definite  disappointment. I very much understand that in databases (all spreadsheets do not have to be databases) a row represents a record and that all data elements (fields or cells) in that record should remain with it at all times. However, I have many spreadsheets that contain smaller related but independent tables where it is more convenient to have them displayed as such (on a single sheet, which also saves paper when printing). I also used Excel for lists and forms (with/without merged cells) basically for anything I wish to order or prioritize (e.g. weekly calendar for appts and things to do that may be categorized - work, church, home) that I'd like to sort independently but display together. I understand there are different ways to do such things, but for me, such an approach is expedient, efficient and organized. Quite often I can design something in Excel that would preclude my need to purchase a stand alone app for similar functionality (stock tickler, TTD, travel itineraries, etc.). 

I think/wish Numbers should/would provide such functionality rather than restrict the ability to utilize different parts of the spreadsheet independently and allow us to be responsible for our own data integrity. Perhaps they could/should include a sheet based toggle (such as 'treat rows as unique or exclusive records' (or some such verbiage)) that can be toggled on/off (maybe just when creating a new sheet) thus allowing for selected cell sorts on the fly in the sheet. I believe this would make Numbers more flexible and useful. I understand that this is just one person’s opinion, but I suspect many others would be happy with such a feature as well. 

Thanks! Cheers!

Monty Hampton wrote:

I am a newly committed convert to Numbers since Excel has now gone to a cloud based subscription.

I have many spreadsheets that contain smaller related but independent tables where it is more convenient to have them displayed as such (on a single sheet, which also saves paper when printing).

Microsoft still has a 2019 Home & Student version of Office that is not subscription. They hide it well. It does not include all the apps, just Word, Excel, and Powerpoint. The apps are fully functional but do not have some of the newer features you would get with 365. Microsoft kept them slightly dumbed down (even when compared to the 2019 version of 365). Microsoft obviously does not want you to buy it. To heck with that, I'll drop Office altogether if forced into a subscription.

https://www.microsoft.com/en-us/microsoft-365/p/office-home-student-2019/cfq7ttc0k7c8?activetab=pivot%3aoverviewtab

In Excel, each worksheet is one monolithic table. It truly ***** to have "smaller independent tables" on one worksheet in Excel because they almost always share columns or rows, meaning you cannot set the column width or row heights independently for the multiple "independent" tables. Numbers, on the other hand, is built for smaller independent tables. You can have multiple tables on one sheet that are independent in every way from one another. You can also sort those independent tables independently without affecting the other tables. If you import an Excel file, you can break apart the monolithic table into the independent smaller tables. There was a thread recently on doing just that.

Thanks for the info and insight, badunit. I appreciate it. Though I didn't mention it (for apace considerations), I was aware of the Home and Student version, but I seemed to recall reading somewhere where it was not going to be supported (or they would not commit to supporting it) much longer. I also didn't mention that since I upgraded to Big Sur - all my Excel files open upside down and sometimes backward. I've read there is a fix for it but it was just further motivation for me to switch to Numbers.

I understand your very valid points regarding Excel's formatting limitations, but I've learned to work around them over the years. If your tables are adjacent you can indecently control column width and can independently control row height if they are stacked. That has been sufficient for me. I'll look forward to working with Numbers ability to handle independent tables. It does sound intriguing and advantageous in that regard.

Still, the more laborious process of sorting selected cells is a big adjustment for me (and apparently others). It is something I am certain I do daily, so it will take the longest to which to adjust. If it is technically possible, I still think it would be great to toggle (or define) the sheets one way or the other or to otherwise somehow allow for such functionality.