Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 5
Default excel 2011 won't sort alphabetically

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!
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,872
Default excel 2011 won't sort alphabetically

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
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,045
Default excel 2011 won't sort alphabetically

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.
  #4   Report Post  
Junior Member
 
Posts: 5
Default

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!

Quote:
Originally Posted by Claus Busch View Post
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
  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,872
Default excel 2011 won't sort alphabetically

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


  #6   Report Post  
Junior Member
 
Posts: 5
Default

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!


Quote:
Originally Posted by Claus Busch View Post
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
  #7   Report Post  
Junior Member
 
Posts: 5
Default

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.

Quote:
Originally Posted by Claus Busch View Post
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
  #8   Report Post  
Junior Member
 
Posts: 5
Default

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


Quote:
Originally Posted by Ron Rosenfeld[_2_] View Post
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.
  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,045
Default excel 2011 won't sort alphabetically

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
  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2
Default excel 2011 won't sort alphabetically

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I sort alphabetically in Excel seh60025 Excel Discussion (Misc queries) 1 October 26th 06 10:25 PM
How do I sort contents alphabetically in Excel? Lila Bean Excel Discussion (Misc queries) 1 May 4th 06 05:41 PM
How do I sort my excel list alphabetically by last name? JIMC Excel Discussion (Misc queries) 2 February 21st 06 11:40 PM
How do I sort in Excel alphabetically? Jennifer Excel Discussion (Misc queries) 2 January 20th 06 05:42 PM
How do I sort alphabetically by last letter in Excel? andrewcodd Excel Discussion (Misc queries) 3 July 27th 05 04:09 PM


All times are GMT +1. The time now is 05:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"