excel 2011 won't sort alphabetically

Discussion in 'Excel Beginners' started by walkingfern, Dec 22, 2013.

  1. walkingfern

    walkingfern Guest

    I'm trying to sort a spreadsheet of fern information alphabetically
    first by column A (genus) and then by column B (species). Columns C-
    contain additional information.

    I've selected data sorting in the order of Column A then Column B.

    However, it consistently puts some items out of order for column B
    making it difficult for me to check the species information. I've doubl
    checked for extra spaces, there are no extra characters. I don'
    understand what's happening.

    Sorted alphabetically, Dryopteris marginalis will be listed befor
    Dryopteris goldiana.

    Or it will list
    Dryopteris marginalis
    Dryopteris marginalis
    Dryopteris intermedia
    Dryopteris marginalis
    Dryopteris marginalis.

    Can anyone help? Thank you
    walkingfern, Dec 22, 2013
    1. Advertisements

  2. walkingfern

    Claus Busch Guest


    Am Sun, 22 Dec 2013 19:24:34 +0000 schrieb walkingfern:
    is column A correctly sorted?

    Claus B.
    Claus Busch, Dec 23, 2013
    1. Advertisements

  3. I don't think we'll be able to help without a copy of the dysfunctional workbook. I see nothing apparent from what you've posted to explain this behavior. Hidden (non-printing) characters are the most likely explanation. Especially if you are copying this information from the Web, the possibility of <nbsp> characters can mess up your sorting.
    Ron Rosenfeld, Dec 23, 2013
  4. walkingfern

    walkingfern Guest

    Thank you for replying!

    Yes, Column A is correctly sorted. However, Column B is all awry.
    thought that maybe it had something to do with the sort order of Colum
    C, so I eliminated that from the "sort by" but it's still out of order

    All cells are formatted as text.

    walkingfern, Dec 23, 2013
  5. walkingfern

    Claus Busch Guest


    Am Mon, 23 Dec 2013 16:51:30 +0000 schrieb walkingfern:
    try following macro for your column B and then try sorting again:

    Sub Test()
    Dim LRow As Long
    Dim rngC As Range

    LRow = Cells(Rows.Count, 2).End(xlUp).Row
    For Each rngC In Range("B1:B" & LRow)
    rngC = WorksheetFunction.Clean(Trim(rngC))
    End Sub

    Claus B.
    Claus Busch, Dec 23, 2013
  6. walkingfern

    walkingfern Guest

    Well, this was a new experience. ;-)

    1. I've never opened Visual Basic.
    2. I've never even used a macro.
    3. I have a Mac.

    Found online instructions for how to create a macro in Visual Basic.
    Copied and pasted the macro.
    Hit the F5 key.
    Same problem. It sorts on Column A and Column B has a mind of its own.

    Tried again and got a runtime error. Hit "debug" and it highlighted th

    LRow = Cells(Rows.Count, 2).End(xIUp).Row

    One of these days, I'll have to try learning new things. I just wish
    understood why Excel doesn't follow its own instructions.

    Thank you for providing me with a new experience!
    walkingfern, Dec 24, 2013
  7. walkingfern

    walkingfern Guest

    I've now moved from ferns to woody plants. Same problem.

    Is it possible for me to post a copy of the workbook page?

    No, I haven't copied anything from the web. And I looked to make sur
    that blank spaces weren't lurking.

    Huh...that's interesting. I just noticed something that might be causin
    the problem, but don't know what is causing it.

    I copied and pasted a set of cells. Although in the workbook, they ar
    all separate cells and I need to tab or click to get from one to th
    next, when I copy and paste, a number of them are showing the text o
    the cells running together without spaces.

    Note the PiceaabiesGregoryana Parsonii on the first line. The othe
    Picea abies are at the bottom and have a space between "Picea" an

    Then again, that doesn't fully explain why Piceaspecies is befor
    Piceajezonensis. But, it might give a clue????

    Picea abies Gregoryana Parsonii
    Picea glauca Densata
    Picea glauca Yukon Blue
    Picea glauca Jean's Dilly
    Picea glauca? cultivar?
    Picea species, unknown medium; globular
    Picea jezoensis hondoensis
    Picea koyamae
    Picea mariana Aureo marginata
    Picea omorika
    Picea omorika
    Picea omorika
    Picea omorika
    Picea orientalis Losely
    Picea orientalis Gaul Aurea
    Picea pungens Pendula
    Picea pungens Henry B Fowler
    Picea pungens Henry B Fowler
    Picea pungens Hunnewelliana
    Picea pungens Hunnewelliana
    Picea abies Nidiformis
    Picea abies Nidiformis

    walkingfern, Dec 24, 2013
  8. There are various free sites where you can post files for sharing. I use SkyDrive but DropBox also seems popular. Instructions for the use are on the various sites.
    If you want, you could email me a file at this address: (reverse the letters and make the obvious substitutions.) mocTODenilnodlefnesorTAnor
    Ron Rosenfeld, Dec 24, 2013
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.