#1   Report Post  
peter
 
Posts: n/a
Default sorting help

Bear in mind, I am new to excel. Say I have a column with the
following entries:

2
1
2-a
3

When I use the "sort ascending" button on this column it comes up:

1
2
3
2-a

What I would like is the following:
1
2
2-a
3

Can anyone help?

Thanks,
Peter

  #2   Report Post  
David McRitchie
 
Posts: n/a
Default

Hi Peter,
You would have to make sure that Excel knows that each
cell is text. Excel sorts numeric cells before text cells.
Format the column as text, and then reenter each cell
with F2 then Enter; or use a macro to effect the reentry.
http://www.mvps.org/dmcritchie/excel/join.htm#reenter
one of the macros there should help, probably the first one
under that topic.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"peter" wrote in message oups.com...
Bear in mind, I am new to excel. Say I have a column with the
following entries:

2
1
2-a
3

When I use the "sort ascending" button on this column it comes up:

1
2
3
2-a

What I would like is the following:
1
2
2-a
3

Can anyone help?

Thanks,
Peter



  #3   Report Post  
peter
 
Posts: n/a
Default

Thank you, David!

  #4   Report Post  
peter
 
Posts: n/a
Default

OK, that worked for my example. In my actual spreadsheet, I am copying
data from one sheet to the next where I sort it. I wasn't able to get
it to work when I copied and pasted data. Do I format the original
sheet as text and the sheet on which it is sorted? Maybe I didn't try
hard enough, but I'm wondering if you have a quick answer.

  #5   Report Post  
David McRitchie
 
Posts: n/a
Default

Hi Peter,
Since those are probably constants, that actually
would simplify that if that is all there is to it.

Format the output area as text, and copy the
input area, the paste as values (edit, paste special, values)

My Excel is 2000, just in case that doesn't work for you.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]


"peter" wrote in message oups.com...
OK, that worked for my example. In my actual spreadsheet, I am copying
data from one sheet to the next where I sort it. I wasn't able to get
it to work when I copied and pasted data. Do I format the original
sheet as text and the sheet on which it is sorted? Maybe I didn't try
hard enough, but I'm wondering if you have a quick answer.





  #6   Report Post  
peter
 
Posts: n/a
Default

Ok I think I got it this time. Thanks!

  #7   Report Post  
peter
 
Posts: n/a
Default

It's still not working on my actual sheet. What I do, step by step,
is:

1. I import a text file which is a list of data running vertically.
One entry per line.
2. On the sheet to which it is imported, I have set it up so certain
cells are assigned the contents of each imported cell, basically going
across.
3. I copy the contents of these cells into a new sheet and paste them
starting on the next available line.

I deleted the contents of all cells and formatted them to text, then
tried to import again. It still gave me the same problem of putting
"1-a" at the botttom of the sheet when I sorted. What am I missing? I
wonder if I should rebuild the sheets from scratch. I can do this but
it is time consuming so I'd like to know if you think that would work.
Thanks,
Peter

  #8   Report Post  
David McRitchie
 
Posts: n/a
Default

If you import them again, then you have to start over again.

If the extension is .csv then change it the .txt so that the
text import wizard will give you a change to format the column
as text beforehand.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"peter" wrote in message oups.com...
It's still not working on my actual sheet. What I do, step by step,
is:

1. I import a text file which is a list of data running vertically.
One entry per line.
2. On the sheet to which it is imported, I have set it up so certain
cells are assigned the contents of each imported cell, basically going
across.
3. I copy the contents of these cells into a new sheet and paste them
starting on the next available line.

I deleted the contents of all cells and formatted them to text, then
tried to import again. It still gave me the same problem of putting
"1-a" at the botttom of the sheet when I sorted. What am I missing? I
wonder if I should rebuild the sheets from scratch. I can do this but
it is time consuming so I'd like to know if you think that would work.
Thanks,
Peter



  #9   Report Post  
peter
 
Posts: n/a
Default

Ok, David, I am making progress and I very much appreciate your help
The file is already a .txt. I did not think of formatting it using the
import wizard. That seems to solve my problem, but I won't know for
sure until I go to work tomorrow and test it. The text file changes
daily, so will I be able to format it once using the text import wizard
and then refresh it automatically, each day, with it adhering to the
text format? Secondly, I discovered another problem. When I format
the rows to text, it compares character by character, so 27 would be
placed above 3. I read the help file and found out why, but I did not
find out how to change this, so 3 would be before 27, and 1-a would be
between 1 and 2. Any thoughts?


Thanks,
Peter

  #10   Report Post  
David McRitchie
 
Posts: n/a
Default

Hi Peter,
On a regular basis you would probably want to run a macro
to read in the file and format as you want, because the
input wizard is manual -- you can record a macro for the
entire process.

From you original note, I thought you were looking for a text
sort. Looks like you want the initial digits sorted numerically
and the rest sorted as text. Harlan Grove posted some neat
usage of Regular Expressions used within User Defined Functions.
I've tried to put that information on a web page, with some additional
references supplied by Harlan, and I wrote some code to help me
examine usages of Regular expressions.

Extraction of a Group of Digits and Dashes, from postings by Harlan Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm
This will involve two additional helper columns.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"peter" wrote in message oups.com...
Ok, David, I am making progress and I very much appreciate your help
The file is already a .txt. I did not think of formatting it using the
import wizard. That seems to solve my problem, but I won't know for
sure until I go to work tomorrow and test it. The text file changes
daily, so will I be able to format it once using the text import wizard
and then refresh it automatically, each day, with it adhering to the
text format? Secondly, I discovered another problem. When I format
the rows to text, it compares character by character, so 27 would be
placed above 3. I read the help file and found out why, but I did not
find out how to change this, so 3 would be before 27, and 1-a would be
between 1 and 2. Any thoughts?


Thanks,
Peter





  #11   Report Post  
peter
 
Posts: n/a
Default

Ok, I'll have to have to look at that code and play around some. Thank
you for your help!

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
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 2 April 30th 23 07:42 PM
Sorting Spreadsheet with Merged Fields Linda L Excel Discussion (Misc queries) 1 January 22nd 05 01:58 AM
sorting detail rows - summary row in an outline ExcelSteve Excel Discussion (Misc queries) 1 December 5th 04 03:40 PM
Sorting problem Klaus Excel Discussion (Misc queries) 3 December 4th 04 02:55 AM
sorting question Brian Excel Discussion (Misc queries) 4 November 28th 04 01:30 PM


All times are GMT +1. The time now is 07:18 PM.

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"