1 2 Previous Next 15 Replies Latest reply: Mar 19, 2013 12:42 PM by Hiroto
RoseBryanna Level 1 Level 1 (0 points)

I'm trying to do something simple - I thought.  I have a table and I want to pick one column and sort the items in THAT column only, and leave the rest of the table intact.  I end up with the column sorted as I wished, but the ROWS are also being sorted (it appears in some logical order - but a logic I can't figure out).  I think I've used every combinations available in the reorganize window, and I have tried multiple ways of selecting, etc., with no luck.

 

I've been using Numbers since it came out. I've done this simple operation many times before, but I've never had this prob before.  Numbers 2.1 (436)


iMac, Mac OS X (10.5.2), Intel Core 2 Duo processor
  • 1. Re: Sorting a column in Numbers
    RoseBryanna Level 1 Level 1 (0 points)

    Addendum:  I've looked more thoroughly at related questions and the answer appears to be that you have to take the columns out to make a seperate table, do the operation there, and then replace them in the table???!!!  AND a lot of unpleasant stabbing inbetween those simple lines.  Frankly I am shocked the way everyone talks to each other in this discussion!

     

    Is this still the answer - remove columns as above?  If so it IS pretty poor (although I won't follow the examples of language provided above....).

     

    Since I've done this many times before (not recently tho) and never had problems, what do you think is the difference?

     

    Any suggestions other than removing the columns  and then replacing them?

  • 2. Re: Sorting a column in Numbers
    Barry Level 7 Level 7 (29,180 points)

    Hi Rose,

     

    I think your memory may be playng tricks on you.

     

    Numbers sets up sorting on a model that considers all data in one row or a table to be part of a single record. You may sortt the rows of a table based on the content of one or more columns, but you cannot sort a single column without rearranging all of the others.

     

    If you want to sort the data in a single column, you must remove the column (or its data) from the table to do the sort. After sorting, you can return the sorted data (or the entire column) to the original table, or keep it in a separate table where it may be sorted at will.

     

    Regards,

    Barry

  • 3. Re: Sorting a column in Numbers
    RoseBryanna Level 1 Level 1 (0 points)

    Thanks Barry.

     

    Yes, who knows how I "remember" doing it.  It is pretty crazy to me that I have to go through all of that just to sort one column.  I hope the Numbers Gurus will fix it some day.

     

    Don't you thinks it a bit predjudiced to only sort the rows?

     

    Anyway you described exactly what was happening, and I'll just put them in another table.

     

    Thanks for your information,

     

    Rose

     

    Message was edited by: RoseBryanna

     

    Message was edited by: RoseBryanna

  • 4. Re: Sorting a column in Numbers
    Barry Level 7 Level 7 (29,180 points)

    "I hope the Numbers Gurus will fix it some day.

    Don't you thinks it a bit predjudiced to only sort the rows?"

     

    I suspect the 'Numbers Gurus' see no need to 'fix' something that isn't broken. Having Numbers's sorting mimic a database is a design decision, and to my mind, it's a good one, and part of a larger view of what Numbers is and how its documents should be built.

     

    Most spreadsheet applications are based on a single large table per document, and with that model, it makes some sense to provide for sorting only part of the table. Numbers model is based on several independent (but interactive) tables, each used for a single purpose task. Within each table each row is a unit of related data that stays together. With that model, sorting the rows rather than the individual cells makes sense, and anything that needs to be sorted separately from the rest of the row would be better placed in a separate table.

     

    "Prejudiced"? I'm not sure what you mean by the term. Every piece of software (or hardware) involves the judgement of its designers, and much of that judgement has to take place during the development process, which usually occurs prior to the product's release. So, yes, the judgement that sorting by rows is the best route was a 'pre release' judgement. But that's also true of other spreadsheet applications whose developers judged (prior to the release of that product) that sorting only selected columns was the way to go.

     

    I (and several others who were doing the data processing for a regional science fair the year that Apple decided to introduce 'sorting only selected columns' in AppleWorks) spent the better part of a day and night repairing the effects of that unfortunate decision (while the students waited less than patiently for their results)!

     

    That said, if you want changes to the way numbers (or any other Apple application) works, this user-to-user forum isn't the place to make your request. Current Apple applications all have an Application menu (in Numbers, called the 'Numbers' menu) and the ones I've seen all have an item in that menu named "Provide application Feedback." Requests and other feedback provided via that link go directly to Apple, and have resulted in changes to subsequent versions of a number of applications (including Numbers).

     

    Regards,

    Barry

  • 5. Re: Sorting a column in Numbers
    Dutch46 Level 1 Level 1 (0 points)

    First, I think that rather than castigating the person for making the suggestion, you should either contribute something to that person's knowledge about how he or she can get the wanted results or just keep out of the discussion.  Excel with which I have done a lot of work allows you to sort only the columns that you select. 

    That way any data that is dependent on a quantity in the new sort will still calculate correctly.  Numbers, despite checking the selection "sort selected columns" sorts the enire database thus making that selection superfluous.  In Numbers, I have to either remove dependent column and later reinstall it or fix the positions of the dependent variable.  A lot of extra work. 

     

    If I wanted a database application I would have used a database but I wanted to create a very simple spreadsheet that adds a value in one column to a total in another column and that I can resort by date if I have to add an item that is out of the existing date order without having to complicate the matter with database programming or removing and readding columns.

     

    Duch46   

  • 6. Re: Sorting a column in Numbers
    Wayne Contello Level 6 Level 6 (13,620 points)

    Dutch,

     

    What you have described is something you can do In Numbers.  There is, however, no need to sort by only one column to achieve your goal.  If you add a new record AND you make all your formulas sort friendly then sorting will work flawlessly.

     

    What you may be missing is that there are formulas that will not "survive" a sort.  If you provide a specific example we may be able to help you.

     

    Regards,

    Wayne

  • 7. Re: Sorting a column in Numbers
    Dutch46 Level 1 Level 1 (0 points)

    Here is a part of the spreadsheet.

     

     

    The only math is in column D (Total) and it is as follows, D1 is a fixed value of $408.00.  In the next row down the math in column D would be D1+C2, in row 3 it would be D2+C3 and so on. If I now add an entry on 2/25/13 and wish to sort the data in date order all I need to do is sort columns A-C in date order and the math built into column D takes care of the addition.  Despite selecting the "sort selected columns" option, the application sorts all four columns carrying the values that are in columns D along with the sort.  It is, in effect, fixing those cells in place even though I specified floating cell references or rather I did not select the option to fix the cells in place. This is not good.  If the spreadsheet is small enough so that I can see all of the cells I can see the error and fix it but I certainly would not want to take a chance on working with behavior like that in a multi hundred cell spreadsheet application.

     

    I duplicated the spreadsheet in Excell and repeated the sort and it worked fine.   

     

    If you want to take a look at the actual  spreadsheet I will be maore than happy to send it to you to take a look at.  Send me an email link and I will ship it out to you.

     

    Thank you very much for the very prompt response.

     

    Tom Belstler

    tbelstler@gmail.com

    Dutch46

  • 8. Re: Sorting a column in Numbers
    Dutch46 Level 1 Level 1 (0 points)

    Oops, the spreadsheet evidently didn't survive so here it is.

     

  • 9. Re: Sorting a column in Numbers
    Dutch46 Level 1 Level 1 (0 points)

    The spreadsheet didn't survive the second attempt to send it either.  Sorry.

  • 10. Re: Sorting a column in Numbers
    Barry Level 7 Level 7 (29,180 points)

    Dutch46 writes:

    "The only math is in column D (Total) and it is as follows, D1 is a fixed value of $408.00.  In the next row down the math in column D would be D1+C2, in row 3 it would be D2+C3 and so on. If I now add an entry on 2/25/13 and wish to sort the data in date order all I need to do is sort columns A-C in date order and the math built into column D takes care of the addition."

     

    This sounds similar the the calculation done in the current balance column of the Checking Register template supplied with Numbers '09. Here's a sample with the columns you describe. I've assigned random dates to the transactions to provide three columns with different sort results. All sorts are 'ascending'.

    Original order:

    Picture 2.png

    Resorted on Date

    Picture 3.png

    Resorted on Item:

    Picture 4.png

    Formula, entered in D2, then filled down the rest of column D:

    =OFFSET(D$1,ROW()-2,0)+OFFSET($C$1,ROW()-1,0)

     

    Row 1 is defined as a Header row. Header rows are not included in sorts.

     

    "Despite selecting the "sort selected columns" option, the application sorts all four columns carrying the values that are in columns D along with the sort."

     

    Unless someone has slipped it in while I wasn't updating, there is no "sort selected columns" option in numbers '09. A sort may be based on values in one or more columns, and the user may choose to sort either the whole table or "selected rows," but it is always full rows that are sorted.

     

    "The spreadsheet didn't survive the second attempt to send it either."

     

    Spreadsheet (and other files) can't be attached to messages in these forums. You can post an image file, such as the screen shots in this post, using the camera icon at the top of the composition box.

     

    To take a screen shot of part of the screen, place the mouse pointer at one corner of the area you want to include, then press shift-command-4. The arrow pointer will change to a crosshair.

    Press and hold the (left) mouse button, and drag to create a rectangle enclosing the screen area wanted in the shot.

    Release the mouse button to complete the shot.

    The screen area will be captured as a .png file named Picture or Screenshot with either a number or the date and time added (depending on OS version).

     

    To insert the image in your post:

    Click the camera icon.

    In the dialogue that opens, click Choose image.

    Navigate to your Desktop, locate and click on the file to select it.

    Click Choose.

    Click Insert image.

     

    Regards,

    Barry

  • 11. Re: Sorting a column in Numbers
    Yellowbox Level 5 Level 5 (4,580 points)

    Dutch46,

     

    I note your reply (16 March 2013) to Barry's post on 4 September 2011. You highjacked a thread that is 18 months old and then you criticised Barry's reply.

     

    These Apple Support Communities are user-to-user discussions. Volunteers give their time and knowledge to try to help.

     

    Barry is a gentleman. I have followed Barry's contributions to various Apple Support Communities. Barry has always been helpful and constructive, contributing to the knowledge of the Original Poster whilst giving an insightful suggestion or solution. In my opinion, he has never 'castigated' anyone.

     

    Barry was explaining that Numbers is not Excel, and Excel is not Numbers. Those two apps apply to two different ways of thinking. Excel is a spreadsheet and Numbers mimics a database.

     

    Please read:

     

    - https://discussions.apple.com/static/apple/tutorial/etiquette.html

     

    - https://discussions.apple.com/static/apple/tutorial/tou.html

     

    - the Numbers User Guide. You can download it from the Help Menu in Numbers.

     

    - How To Ask Questions The Smart Way

    http://www.catb.org/esr/faqs/smart-questions.html

    Until you learn how to phrase your question, you will get no help from me.

  • 12. Re: Sorting a column in Numbers
    Dutch46 Level 1 Level 1 (0 points)

    I did say "selected columns"  and that was a mistake, it does say "selected rows".  But you did put your finger on the problem when you stated that "A sort may be based on values in one or more columns, and the user may choose to sort either the whole table or "selected rows," but it is always full rows that are sorted.  That is a serious problem.  In a spreadsheet one may not always wish to sort the entire row because it may destroy data that one wishes to preserve in its original place. In large multi thousand cell spreadsheets one may only be dealing with a section of the data and forcing an unwanted sort will present problems and may well invalidate the results.  As to the formula you gave me, why do I need to enter this complex piece of logic when a simple add this to that will do the trick?  I wish to point out that a spreadsheet is supposed to be an aid to the user and  kept as simple as possible.

     

    I am 66 years old and have worked with spreadsheets in a professional capacity since they were invented over 40 years ago beginning with the very earliest ones that only added, subtracted, multiplied and divided numbers whose cells could not be moved from their original position in the spreadsheet.  Their values could be changed, but the cell location couldn't be moved without redoing the entire spreadsheet.  I have used many incarnations of spreadsheets from companies that no longer exist today.  Some were good, some were not so good but the very best is Microsoft Exel with which I have worked extensively.  It is the gold standard of spreadsheets and is remarkably versatile and the addition of Visual Basic has made it even more so.  My only complaint with it was that they had a tendency to bury items in the menus and that, on very infrequent occasions, the 64K X64K limitation forced me to use other, less convenient methods of data analysis. But, that was what I was paid to do.  The sort that I described to you works fine in my 2012 version of Excel so I will be using that instead of wrestling with the quirks of Numbers. 

     

    I bought the Numbers spreadsheet because it was cheap and I wanted to experiment with it to compare it to Excel.  I will continue to play with it to see how it works but being unable to sort correctly puts a very severe limitation on experimentaion.  I like the Apple operating system and use it whenever possible.  It is much better from many viewpoints than the Microslush system.  But, Microsoft does have some really good apps.

     

    Once again I would like to thank you for your time, effort and promptness in replying to my questions.  It is a far superior experience than any I have had with Microsoft products.  

     

    My Best Regards

    Tom Belstler

    tbelstler@gmail.com

    Dutch46

  • 13. Re: Sorting a column in Numbers
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Tom,

     

    Numbers is cheap, and it doesn't have all the bells and whistles of Excel, but it doesn't sort incorrectly either.

     

    It follows the database model of always keeping the fields of a given record together. It will never take a field's entry from one record and attach it to another record, which is what would happen if you allowed the entries in one column to stay put and the entries in another column to sort.

     

    Many of us here are as old and as experienced as you. Many much older.  Our eyesight may fade and at some point we may be more easily confused, but I believe that those of us who posted to this thread can tell a spreadsheet gimmick from a protocol that conforms to standards. As Barry noted way up the thread, in Numbers you aren't stuck with a sea of cells that you need to partition when sorting. In Numbers one isolates related data into single-purpose tables, eliminating the need to exclude columns when sorting. If you like the monolithic look and feel of Excel, use it rather than trying to pound Numbers into the Excel mold.

     

    Jerry

  • 14. Re: Sorting a column in Numbers
    Wayne Contello Level 6 Level 6 (13,620 points)

    Tom,

     

    In addition to what Barry and Jerry, have already posted...  I have been in Electrical Engineering field for 20 years.  I have been programming computers since my Commodore Vic 20 days (even had a 16 kB upgrade card).  As anyone will tell you, there is a right tool for the job (sometimes more than one).  There are tools that are definitely NOT the right tool.

     

    For what you pay Numbers is a GREAT tool.  It is not MS Excel and makes not attempt to be.  You should know that Numbers cannot handle the number of rows and columns as excel.  There are OTHER strenghts that it does have such as actually presenting data such as:

    Screen Shot 2013-03-17 at 12.22.15 PM.png

     

    In excel this would be a mess.  I hope you can appreciate that people in this forum volunteer their time to answer questions.  None of us work for Apple but we do have extensive knowledge of the ability to solve problems.  We, however, have no more ability to affect change with Numbers than we have on the weather.

     

    Once you see the strengths of Numbers you may see Excel in a different light.  Keep opsting questions here to learn the Numbers way.  Once you understand how segregating and organizing data makes for a simpler solution you may find Numbers more compelling.

     

    Regards,

    Wayne

1 2 Previous Next