Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I think Excel 2000 must be broken, but I will ask here first. I haven't used Excel much in the last few years since I retires, but the Lounge always used to dazzle me with its collective wisdom.

    I want to sort the 13 rows in ascending sequence by column A. (Sorry for the lousy formatting - i added some underscores to make the headings line up slightly. Also, "A", "B", etc. are arbitrary column labels I used for the sake of brevity - the spreadsheet columns are actually O through T. [Never know what sort of arcane info might prove useful.])

    _ A _ B C _ D _ E Lap
    16.5 45 64 -19 124 1
    15.4 68 57 11 129 2
    11.7 113 38 74 129 3
    18.8 26 86 -60 121 4
    13.5 89 35 54 126 5
    14.2 65 57 8 116 6
    17.1 64 106 -42 129 7
    13.0 97 71 26 127 8
    13.7 80 46 34 127 9
    15.2 57 77 -19 125 10
    14.9 66 110 -43 124 11
    16.1 28 57 -29 119 12
    14.6 57 38 19 123 13

    If I highlight the entire area, including the header, starting at the top left, and click on the "ascending sort" icon (or if I use Data / Sort / Sort by (A) Ascending with Header row), I get two different results depending on how I created the numbers in column A:
    • If I compute the numbers [cell A for Lap 1 =IF($A24.0,+A24,"") ], nothing sorts
    • If I manually type in the numbers in column A, column A sorts but only column A


    Reinstall?

    PS - I used to be proficient. I feel like I've entered the twilight zone - this can't be happening!

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Give this a try...
    Highlight all of your data including the column headings and give the data a range name of Database. Once your data is named Database, you can sort on any column from within any item of your data. Click on any item within the Database and use the up or down arrows to do the sorts.

    "Database" is a unique and powerful range name to versions of Excel.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Sorting can be tricky but here's a few things I've learned over the years.

    1. Generally you DON'T want to select all the data just select one of the cells {this assumes that you want to sort entire rows, which is usually the case, and there aren't any blank rows in the data.)
    2. Pay attention to the selections in the sort dialog box. Excel is usually pretty good at determining if you have headers or not but in case it gets it wrong you want to check/uncheck the "Has Headers" box.

    Post back if you're still having problems and upload a sample workbook if possible.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 987 Times in 917 Posts
    Your formula "=IF($A24.0,+A24,"") " isn't valid. Please paste the correct one and we'll suggest.

    cheers, Paul

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts
    All -
    First, thank you for looking at this.
    Second, I am attempting to sort on a calculated field (described in the original posting and the formula is shown below in my response to P T)
    Third, I am now simply using the "Sort Ascending" icon because it's faster, but I have gone through the Data / Sort dialogue box numerous times with identical results
    Fourth, I've done sorting in the past and didn't have any problems. I was surprised to find during my Google search numerous references to problems with "sort" in Excel
    Fifth - a link to the workbook is http://www.woodloon.com/Impact.xls (hopefully this worked).

    Tim Sullivan -
    Give this a try...
    Highlight all of your data including the column headings and give the data a range name of Database. Once your data is named Database, you can sort on any column from within any item of your data. Click on any item within the Database and use the up or down arrows to do the sorts.

    "Database" is a unique and powerful range name to versions of Excel.
    I'm not sure if I grasp what you want. I highlighted the cells A24:E37, Insert / Name / Define / "Database" - that didn't seem to accomplish anything.

    Retired Geek -
    Sorting can be tricky but here's a few things I've learned over the years.

    1. Generally you DON'T want to select all the data just select one of the cells {this assumes that you want to sort entire rows, which is usually the case, and there aren't any blank rows in the data.)
    2. Pay attention to the selections in the sort dialog box. Excel is usually pretty good at determining if you have headers or not but in case it gets it wrong you want to check/uncheck the "Has Headers" box.

    Post back if you're still having problems and upload a sample workbook if possible.
    1. I selected just the first cell in the array, clicked "Ascending Sort" - nothing changed
    2. I selected just the first column in the array, clicked "Ascending Sort" - nothing changed
    3. I have tried with and w/o the header line (and I note Excel does seem very clever at finding the appropriate label)

    How do I include a workbook? Is there any way simpler than FTP to a remote site and provide a link? At any rate, I did it, I think - see above.

    P T -
    The actual cell is:
    =IF($P6>0,U6,"")

    Good luck!

  6. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - Attached is a revision to your file named Impact A.xls. Notice the area that the range name Database covers.

    In this pic below, the cursor was in cell Q10 then hit the A-Z sort button. Try it, place the cursor anywhere within database and hit one of the sort buttons.
    [attachment=91051:Impact pic.jpg]


    Let us know if it works for you.


    Tim
    Attached Images Attached Images
    Attached Files Attached Files

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    If you delete the blank column "O" it will sort properly w/o creating the named range.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Whew! This is sure confusing. This has become a puzzle for me, but our work sparked in this thread has led me to a workable solution.

    1. I've been trying to sort fields farther down the worksheet, Rows 26-38 and 42-54 after I realized that Excel is sorting the entire row. That would allow me to keep the original input data intact in sequence by lap. I had thought that would solve the problem. Therefore your solutions involving column Q seems even more impressive to me. But I stuck with the simpler rows for the following experiments.
    2. I cut the array from A23:E38 and pasted it into sheet 3. Sort worked just like I have been expecting it to, using whatever column I want as the control.
    3. I cut the array out of sheet 3 and moved it back into the original sheet in the original location and now it works there.
    4. I repeat the same steps on array A42:E54 (which hasn't been cut/pasted) and it doesn't sort.
    5. I cut A24:E54, paste in sheet 3, and it works
    6. Cut it again and return it and now it works in its original location.

    So the problem is solved but with no comprehension. I do not see how the steps I took above would change anything.

    Tim Sullivan -
    1. Your modified worksheet worked fine.
    2. How do you define the area as "Database"? How can I tell that the array has been given a name?
    3. Insert / Name / Define ? If I do that, it doesn't make my original worksheet work like your version does.


    Retired Geek -
    1. I eliminated column "O" - no change that I could tell.

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by hobkirk View Post
    Retired Geek -
    1. I eliminated column "O" - no change that I could tell.
    Sorry it didn't work for you. I'm using 2007 so maybe there are some differences.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by RetiredGeek View Post
    If you delete the blank column "O" it will sort properly w/o creating the named range.
    If column "O" is deleted, the info in cells O56 thru cell O73 will be lost.


    Quote Originally Posted by hobkirk View Post
    Tim Sullivan -[list=1][*]Your modified worksheet worked fine.[*]How do you define the area as "Database"? How can I tell that the array has been given a name?[*] Insert / Name / Define ? If I do that, it doesn't make my original worksheet work like your version does
    We'll start without a range named Database. Do Insert / Name / Define then click on Database and click Delete. Now there should be no range named Database.

    Here is what I do to give a range a name....
    Highlight the area you want to name. In this case highlight cells A6 thru U19.
    In the white box above column A, tyoe the word Database.
    See pic below.....

    [attachment=91058:Impact pic A.jpg]

    Anytime you want to see the list of range names, click on the down arrow of the white box above column A. Click on a name and Excel will highlight the area used for that range.

    Hope that helps.
    Attached Images Attached Images

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Tim Sullivan View Post
    If column "O" is deleted, the info in cells O56 thru cell O73 will be lost.
    Ooops! should have scrolled down!

    However, I like the approach of moving ranges to different sheets, makes management of so many things, including sorting, easier. I used to have a book on spreadsheet style {I'm not home so I can't hunt it up.} that advocated this approach.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •