excel 2011 won't sort alphabetically

[walkingfern]

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

 

[Claus Busch]

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.

 

[Ron Rosenfeld]

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.

 

[walkingfern]

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]

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.

 

[walkingfern]

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!

 

[walkingfern]

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

;1615540']On Sun, 22 Dec 2013 19:24:34 +0000, walkingfer

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

 

[Ron Rosenfeld]

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