Sorting errs

[SG]

Hello there,

I am having an issue with Sorting my data list.


I am having a list/ range which I would like to sort.
Whether I use the standard "sort ascending" button or I use
Data|Filter|autofilter instead (sort ascending) ... somehow my data
does not get sorted ascendingly.
It looks like, 1,1,1,1,5,5,5,5,57,7,1,4,4,4,4,4,4,4,4,12,....etc etc


I have a column labelled "Month" ... in which there appear only the
nrs from 1-12.
OK, it is some imported data from an Essbase system ... but I coerced
the data into real numbers by multiplying them with 1, as I though
tthat this could be the cause of teh problem.
Helas

OK, the data is like 30.000 rows long.
But hey, If I sort ascendingly or descendingly for that matter, on
some otehr fields it works!
I would think that sorting on numbers would be the easiest for excel,
but it does not seem to be true.

Anyone an idea why it bugs ? How to overcome it ?
Thanks in advance,
Jen

 

[Dave Peterson]

I'd guess that your coercion didn't work right. I'd do it one more time.

If that column has nothing in it but what looks like numbers, you could test
before you start.

Put these formulas in a couple of empty cells:

=count(a:a)
and
=counta(a:a)

The top formula will show the quantity of numbers in column A.
the bottom formula will show the number of cells with something in it--number,
text, formulas, anything.

If that's not it, make sure you select the complete range before you sort.
Maybe you didn't include all the rows??????

If both of these don't help, maybe the data isn't what you expect. Are there
extra spaces or those HTML non-breaking spaces in the cells?

 

[Pete_UK]

I think you still have some numbers which are really text values. Try
this formula in a blank cell somewhere:

=COUNT(A1:A30000)

assuming your numbers are in column A, and then try this formula:

=COUNTA(A1:A30000)

Do you get the same answer for both formulae?

If not, then you have some text values. Try this in a helper column:

=VALUE(A1)

and copy down. Apply your sort using this field as the sort key.

Hope this helps.

Pete

 

[Jen]

Hi Dave, Pete,

I coerced my column with copy "1", select column, PasteSpecial, Multiply ...
I did this about 5 times now

It returns this:
=COUNT(F4:F35715) equals 35712
=COUNTA(F4:F35715) equals 35712

If that's not it, make sure you select the complete range before you sort.
Maybe you didn't include all the rows??????

It COULD have been me I admit but no not this time

If both of these don't help, maybe the data isn't what you expect. Are
there
extra spaces or those HTML non-breaking spaces in the cells?

I've not really an idea on how these "characters" most look like ...but in
the cells I tested I don't see any Blanks nor any otehr special characters.

Jen

 

[Dave Peterson]

One more guess...

Your column labeled months contains real dates, but is formatted to only show
the month number.

If that's the case, then the column is going to be sorted by date--not just the
month you want.

If you select a few of the offending cells, and look at the formula bar, do you
see a date or do you see a number (like 1 to 12).

If they are dates, you'll want to use a helper column to extract the month and
use that as the basis for your sort:

In G4 (insert a new column G)
=month(F4)
and copy down to G35715

 

[Jen]

Hi Dave,

Actually that Column IS my Helper-column already!
I used the =month(F4) -strategy there already. I turned that aftwards into
fixed values as -I guess- it would otherwise bloat my file size too
much -seen the 35000 lines-. The column is number-formatted!

BUT, I just discovered ... I grouped the data, putting a 1-level -only-
outline on my sheet (so you see outline nr 1&2, ie. the famous + and - to
open and close data quickly, if you see what i mean).
Before sorting I "opened" all my data ... and sorted then with the famous
catastrophic results.
Removing the outlines sorts the whole range properly! )

Well, the outlines were there "accidentally" from a previous excercise ...
but nevertheless I would suspect that they would remain "position-relative"
and not follow the whole sorting as they were manually set -ie. no subtotals
calculated or so-.
At least I, we know now )
Jen

 

[Dave Peterson]

Thanks for posting back.

I wouldn't have guessed that your data was outlined/grouped.

Hi Dave,

Actually that Column IS my Helper-column already!
I used the =month(F4) -strategy there already. I turned that aftwards into
fixed values as -I guess- it would otherwise bloat my file size too
much -seen the 35000 lines-. The column is number-formatted!

BUT, I just discovered ... I grouped the data, putting a 1-level -only-
outline on my sheet (so you see outline nr 1&2, ie. the famous + and - to
open and close data quickly, if you see what i mean).
Before sorting I "opened" all my data ... and sorted then with the famous
catastrophic results.
Removing the outlines sorts the whole range properly! )

Well, the outlines were there "accidentally" from a previous excercise ...
but nevertheless I would suspect that they would remain "position-relative"
and not follow the whole sorting as they were manually set -ie. no subtotals
calculated or so-.
At least I, we know now )
Jen