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

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?

SG wrote:

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 think you still have some numbers which are really text values. Try
this formula in a blank cell somewhe

=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

On Jun 2, 12:47 am, SG wrote:
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

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" wrote in message
...
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?

SG wrote:

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

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 wrote:

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" wrote in message
...
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?

SG wrote:

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

--

Dave Peterson

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" wrote in message
...
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 wrote:

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" wrote in message
...
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?

SG wrote:

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

--

Dave Peterson

Thanks for posting back.

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

Jen wrote:

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" wrote in message
...
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 wrote:

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" wrote in message
...
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?

SG wrote:

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

--

Dave Peterson

--

Dave Peterson