+ Reply to Thread
Results 1 to 7 of 7

List of numbers not sorting correctly

  1. #1
    Registered User
    Join Date
    10-29-2013
    Location
    home
    MS-Off Ver
    Excel 2007
    Posts
    5

    List of numbers not sorting correctly

    Using Excel 2007, I have a very simple spreadsheet made up of only two worksheets that I am using to track the songs played by a band during a current tour.

    One worksheet is called "Summary" and the other is called "Setlists". In the Setlists worksheet I list every show played (10 shows so far), and every song played from each show. In the Summary worksheet I have very song listed that has been played during the tour in one column (A), then the number of times that song has been played in another column (B). Column B, the number of times each song has been played, is populated by a COUNTIF function that looks at the Setlists worksheet and counts each instance of each song.

    What I want to do, very simply, is to sort on column B in order to display the list of songs from the most played to least played. That is where I run into a problem. When I sort my list of numbers, I end up with a seemingly random list that certainly isn't from most to least and I can't figure out why.

    Is it possible that the COUNTIF function which populates that column of numbers is somehow throwing off the sort? Any help is appreciated!
    Last edited by j_ploski; 10-29-2013 at 06:56 AM. Reason: Added version (2007)

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: List of numbers not sorting correctly

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Contributor
    Join Date
    08-22-2013
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: List of numbers not sorting correctly

    On your Summary worksheet highlight just the numbers (cells with countif function in) and then right click >> sort largest to smallest...then a box should pop up asking if you want to expand your selection to alter the adjacent cells (song names), click ok and then it should sort them all for you.

    Could you attach a copy of the spreadsheet please, it might help if this doesn't work.
    If this answer helped you then could you please click "Add Reputation"

  4. #4
    Registered User
    Join Date
    10-29-2013
    Location
    home
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: List of numbers not sorting correctly

    Thank you for offering to help! My sample spreadsheet, which exactly mimics the behavior I described, is attached.
    Attached Files Attached Files

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: List of numbers not sorting correctly

    This would be one way...
    Attached Files Attached Files
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: List of numbers not sorting correctly

    the sorted list is in the right order but the countif recalculates and adjusts on sort
    so change the countif to
    =COUNTIF(Setlists!$1:$1048576,INDEX(Summary!$A$1:$A$31,ROW()))
    fill down then sort
    Attached Files Attached Files
    Last edited by martindwilson; 10-29-2013 at 10:31 AM.

  7. #7
    Registered User
    Join Date
    10-29-2013
    Location
    home
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: List of numbers not sorting correctly

    That did it martindwilson, thank you very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. numbers not sorting correctly
    By JMTMayor in forum Excel General
    Replies: 15
    Last Post: 08-01-2013, 12:00 PM
  2. [SOLVED] The numbers in an alphanumeric string are not sorting correctly
    By sweetroman in forum Excel General
    Replies: 14
    Last Post: 07-19-2013, 10:46 AM
  3. Replies: 2
    Last Post: 07-18-2013, 06:18 PM
  4. Excel not sorting numbers correctly
    By lpexcel in forum Excel General
    Replies: 2
    Last Post: 04-04-2012, 08:50 AM
  5. [SOLVED] Sorting numbers doesn't work correctly
    By GrammyEmmy in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 06-25-2006, 06:50 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1