Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
martin0642
 
Posts: n/a
Default Excel won't sort numbers correctly


Hi all, bit of a weird one this....

Ok - so I have a large-ish dataset with several columns, one of which
is the client number. This dataset is copied and pasted from and Excel
spreadsheet that contains macros...as well as lots of fancy header rows
and other stuff I dont need for analysis. Hence I copy and paste just
the data and the main header row into a new workbook to manipulate it
and then transfer to other programs for analysis.

The problem is this: The client number column goes from 01 to 685. I
need to add in extra info based on the individuals client number. The
easiest way to do this is to sort on the client number column (and yes
I do select all and then use "data - sort" from the menus)

Thing is..even though I make sure the cells in this column are
formatted as numbers Excel simply wont sort them sensibly. As an
example, the latest dataset im using..AFTER sorting takes this order:
8
24
31
55
55
95
205
228
284
298
685
01
01
01

From here on it sorts on the first digit of the numbers. So I get all
the 01, 01, 03 etc, then 10's, 100's etc then 20's, 200's and so on.

Any clues why its doing this? I cant find values in there that arent
numbers, I've looked for random spaces or other characters in each
cell, I've also made absolutely sure that the cells are formatted as
numbers. The datasets will start to grow so I need to get this sorted
as eventually it will become unworkable to autofill my new column based
on client numbers.

AAAAAAAAAAAAAAAAAAAAAH!
THanks - i feel better for sharing


--
martin0642
------------------------------------------------------------------------
martin0642's Profile: http://www.excelforum.com/member.php...o&userid=27589
View this thread: http://www.excelforum.com/showthread...hreadid=471113

  #2   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

Hi

Your "numbers" there are text strings, and are sorted as text, i.e. "2"
"12", because first are checked ASCII values of 1st characters, and unless
they are equal, the result is returned immediately. To get all entries
sorted as numbers, you have to convert them to numbers, or you have to
modify them so, that they all are equal length - padded with 0's. I.e. "002"
< "012".

When you had numbers as text strings, and formatted the range as numbers, it
isn't enough - for Excel they remain strings. To convert truly:
- format the range with numeric strings as Number or General;
- enter number 1 into some free cell, and copy it;
- select the range with strings, and PasteSpecialMultiply;
- delete the number 1, you entere before.

To modify existing strings to same length:
- determine the length of longest string (let it be n);
- into some free column, enter the formula (I assume your numeric
strings are in column A) like
=IF(TRIM(A2)="";"";RIGHT(REPT("0",n) & TRIM(A2),n))
, and copy it down for same amount of rows, as are data in column A;
- select the renge with formulas, and copy it;
- select first cell with data in column A, and PasteSpecialValues;
- delete the column with formulas.


Arvi Laanemets


"martin0642" wrote
in message ...

Hi all, bit of a weird one this....

Ok - so I have a large-ish dataset with several columns, one of which
is the client number. This dataset is copied and pasted from and Excel
spreadsheet that contains macros...as well as lots of fancy header rows
and other stuff I dont need for analysis. Hence I copy and paste just
the data and the main header row into a new workbook to manipulate it
and then transfer to other programs for analysis.

The problem is this: The client number column goes from 01 to 685. I
need to add in extra info based on the individuals client number. The
easiest way to do this is to sort on the client number column (and yes
I do select all and then use "data - sort" from the menus)

Thing is..even though I make sure the cells in this column are
formatted as numbers Excel simply wont sort them sensibly. As an
example, the latest dataset im using..AFTER sorting takes this order:
8
24
31
55
55
95
205
228
284
298
685
01
01
01

From here on it sorts on the first digit of the numbers. So I get all
the 01, 01, 03 etc, then 10's, 100's etc then 20's, 200's and so on.

Any clues why its doing this? I cant find values in there that arent
numbers, I've looked for random spaces or other characters in each
cell, I've also made absolutely sure that the cells are formatted as
numbers. The datasets will start to grow so I need to get this sorted
as eventually it will become unworkable to autofill my new column based
on client numbers.

AAAAAAAAAAAAAAAAAAAAAH!
THanks - i feel better for sharing


--
martin0642
------------------------------------------------------------------------
martin0642's Profile:

http://www.excelforum.com/member.php...o&userid=27589
View this thread: http://www.excelforum.com/showthread...hreadid=471113



  #3   Report Post  
Gord Dibben
 
Posts: n/a
Default

Martin

Looks to me like the "numbers" were originally entered as text.

Merely re-formatting to number will not change that fact.

Copy an empty cell formatted to General.

Select your data range and Paste SpecialAddOKEsc.

This will force the text to numbers.

You will lose the 0 in the 01 cells, but cannot be helped.

You may wish to format those cells to show as 01 but will still be just 1.


Gord Dibben Excel MVP

On Tue, 27 Sep 2005 09:48:34 -0500, martin0642
wrote:


Hi all, bit of a weird one this....

Ok - so I have a large-ish dataset with several columns, one of which
is the client number. This dataset is copied and pasted from and Excel
spreadsheet that contains macros...as well as lots of fancy header rows
and other stuff I dont need for analysis. Hence I copy and paste just
the data and the main header row into a new workbook to manipulate it
and then transfer to other programs for analysis.

The problem is this: The client number column goes from 01 to 685. I
need to add in extra info based on the individuals client number. The
easiest way to do this is to sort on the client number column (and yes
I do select all and then use "data - sort" from the menus)

Thing is..even though I make sure the cells in this column are
formatted as numbers Excel simply wont sort them sensibly. As an
example, the latest dataset im using..AFTER sorting takes this order:
8
24
31
55
55
95
205
228
284
298
685
01
01
01

From here on it sorts on the first digit of the numbers. So I get all
the 01, 01, 03 etc, then 10's, 100's etc then 20's, 200's and so on.

Any clues why its doing this? I cant find values in there that arent
numbers, I've looked for random spaces or other characters in each
cell, I've also made absolutely sure that the cells are formatted as
numbers. The datasets will start to grow so I need to get this sorted
as eventually it will become unworkable to autofill my new column based
on client numbers.

AAAAAAAAAAAAAAAAAAAAAH!
THanks - i feel better for sharing


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 to make excel not round real numbers when making a histogram? Leedawg Charts and Charting in Excel 1 September 21st 05 07:36 PM
Sort Ascending button - Excel 2003 Ann Scharpf Excel Discussion (Misc queries) 0 May 23rd 05 11:11 PM
Sort Ascending button - Excel 2003 bigwheel Excel Discussion (Misc queries) 1 May 23rd 05 11:10 PM
How do I get Excel to correctly add a column of numbers that have. ljontheroad Excel Discussion (Misc queries) 4 February 9th 05 11:08 PM
Excel Sort function should not sort the cell formatting! Lisa D.N.1 Excel Worksheet Functions 1 December 28th 04 09:37 PM


All times are GMT +1. The time now is 11:52 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"