excel 2011 won't sort alphabetically

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

  1. walkingfern

    walkingfern Guest

    Hi,
    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.

    Example:
    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
     
    walkingfern, Dec 22, 2013
    #1
    1. Advertisements

  2. walkingfern

    Claus Busch Guest

    Hi,

    Am Sun, 22 Dec 2013 19:24:34 +0000 schrieb walkingfern:

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


    is column A correctly sorted?


    Regards
    Claus B.
    --
    Win XP PRof SP2 / Vista Ultimate SP2
    Office 2003 SP2 /2007 Ultimate SP2
     
    Claus Busch, Dec 23, 2013
    #2
    1. Advertisements

  3. On Sun, 22 Dec 2013 19:24:34 +0000, walkingfern <> wrote:

    >
    >Hi,
    >I'm trying to sort a spreadsheet of fern information alphabetically,
    >first by column A (genus) and then by column B (species). Columns C-F
    >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 double
    >checked for extra spaces, there are no extra characters. I don't
    >understand what's happening.
    >
    >Example:
    >Sorted alphabetically, Dryopteris marginalis will be listed before
    >Dryopteris goldiana.
    >
    >Or it will list
    >Dryopteris marginalis
    >Dryopteris marginalis
    >Dryopteris intermedia
    >Dryopteris marginalis
    >Dryopteris marginalis.
    >
    >Can anyone help? Thank you!


    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
    #3
  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.

    Thanks!

    Claus Busch;1615539 Wrote:
    > Hi,
    >
    > Am Sun, 22 Dec 2013 19:24:34 +0000 schrieb walkingfern:
    > -
    > > However, it consistently puts some items out of order for column B,
    > > making it difficult for me to check the species information. I'v

    > double
    > > checked for extra spaces, there are no extra characters. I don't
    > > understand what's happening. -

    >
    > is column A correctly sorted?
    >
    >
    > Regards
    > Claus B.
    > --
    > Win XP PRof SP2 / Vista Ultimate SP2
    > Office 2003 SP2 /2007 Ultimate SP



    --
    walkingfern
     
    walkingfern, Dec 23, 2013
    #4
  5. walkingfern

    Claus Busch Guest

    Hi,

    Am Mon, 23 Dec 2013 16:51:30 +0000 schrieb walkingfern:

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


    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))
    Next
    End Sub


    Regards
    Claus B.
    --
    Win XP PRof SP2 / Vista Ultimate SP2
    Office 2003 SP2 /2007 Ultimate SP2
     
    Claus Busch, Dec 23, 2013
    #5
  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
    line:

    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!


    Claus Busch;1615546 Wrote:
    > Hi,
    >
    > Am Mon, 23 Dec 2013 16:51:30 +0000 schrieb walkingfern:
    > -
    > > Yes, Column A is correctly sorted. However, Column B is all awry. I
    > > thought that maybe it had something to do with the sort order o

    > Column
    > > C, so I eliminated that from the "sort by" but it's still out o

    > order.-
    >
    > 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))
    > Next
    > End Sub
    >
    >
    > Regards
    > Claus B.
    > --
    > Win XP PRof SP2 / Vista Ultimate SP2
    > Office 2003 SP2 /2007 Ultimate SP



    --
    walkingfern
     
    walkingfern, Dec 24, 2013
    #6
  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
    "abies."

    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


    'Ron Rosenfeld[_2_ Wrote:
    > ;1615540']On Sun, 22 Dec 2013 19:24:34 +0000, walkingfer
    > <> wrote:
    > -
    > >
    > >Hi,
    > >I'm trying to sort a spreadsheet of fern information alphabetically,
    > >first by column A (genus) and then by column B (species). Columns C-F
    > >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'v

    > double
    > >checked for extra spaces, there are no extra characters. I don't
    > >understand what's happening.
    > >
    > >Example:
    > >Sorted alphabetically, Dryopteris marginalis will be listed before
    > >Dryopteris goldiana.
    > >
    > >Or it will list
    > >Dryopteris marginalis
    > >Dryopteris marginalis
    > >Dryopteris intermedia
    > >Dryopteris marginalis
    > >Dryopteris marginalis.
    > >
    > >Can anyone help? Thank you!-

    >
    > I don't think we'll be able to help without a copy of the dysfunctiona
    > workbook. I see nothing apparent from what you've posted to explai
    > this behavior. Hidden (non-printing) characters are the most likel
    > explanation. Especially if you are copying this information from th
    > Web, the possibility of <nbsp> characters can mess up your sorting



    --
    walkingfern
     
    walkingfern, Dec 24, 2013
    #7
  8. On Tue, 24 Dec 2013 04:20:43 +0000, walkingfern <> wrote:

    >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 sure
    >that blank spaces weren't lurking.
    >
    >Huh...that's interesting. I just noticed something that might be causing
    >the problem, but don't know what is causing it.
    >
    >I copied and pasted a set of cells. Although in the workbook, they are
    >all separate cells and I need to tab or click to get from one to the
    >next, when I copy and paste, a number of them are showing the text of
    >the cells running together without spaces.


    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
    #8
    1. Advertisements

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.
Similar Threads
  1. 8} geo

    automatically enter alphabetically

    8} geo, Apr 21, 2005, in forum: Excel Beginners
    Replies:
    1
    Views:
    99
    David McRitchie
    Apr 24, 2005
  2. HOW CAN I SORT BY ALPHABETICALLY FROM A

    sort by alphabetically

    HOW CAN I SORT BY ALPHABETICALLY FROM A, May 11, 2005, in forum: Excel Beginners
    Replies:
    3
    Views:
    133
    David McRitchie
    May 12, 2005
  3. tangerine62

    i want to list all my cd's alphabetically

    tangerine62, May 27, 2005, in forum: Excel Beginners
    Replies:
    4
    Views:
    84
    Rodney
    May 29, 2005
  4. tellytubbie

    arrange columns alphabetically in Excel

    tellytubbie, Jun 8, 2005, in forum: Excel Beginners
    Replies:
    1
    Views:
    109
    Barb R.
    Jun 8, 2005
  5. Grd

    sort sheets alphabetically

    Grd, Feb 28, 2007, in forum: Excel Beginners
    Replies:
    5
    Views:
    125
    Jim Cone
    Mar 1, 2007
  6. Grd

    how to sort non-alphabetically

    Grd, May 23, 2007, in forum: Excel Beginners
    Replies:
    2
    Views:
    106
  7. ganna pritesh

    how do i arrange list of names alphabetically in excel?

    ganna pritesh, Mar 18, 2009, in forum: Excel Beginners
    Replies:
    2
    Views:
    127
    Gary''s Student
    Mar 18, 2009
  8. sherryc525

    how do you sort ascend two columns alphabetically

    sherryc525, Jul 21, 2009, in forum: Excel Beginners
    Replies:
    1
    Views:
    96
    Eduardo
    Jul 21, 2009
Loading...