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!

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

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.

Thank you for replying!

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.

All cells are formatted as text.

Thanks!

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

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 the line:

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

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

Thank you for providing me with a new experience!

Did find one small, stupid mistake on my part.

On the line with the runtime error, I changed what I'd typed as a capital letter I (between H and J) to a lower case L.

That made eliminated the error message, but it still didn't sort the column.

Thanks for helping.

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.

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

Then again, that doesn't fully explain why Piceaspecies is before 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

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

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!



Sounds impossible I agree, it's not so simple as setting both to sort
ascending is it?


--
Milton