I have problem with a small database - it will not sort.
That is, it DOES change the data around, but not in any comprehensible way.
Having selected the database block, I requested "sort" on last column,
expecting to see a descending numeric order. I got garbage.
I cannot determine exactly what the order is, but it's certainly neither
numeric nor alpha. At least the "garbage" will "Undo" (reverts to original)
Nor is it related to the underlying reference cells which yield the number
Data Sort option is (correctly) set to sort "top to bottom" in descending
order - expected result, the data re-presented with last column in decending
order from 18 to 0.
..
Construction - 11 columns x 39 rows. No blank columns, rows or cells..
1st 2 columns are text (names), the remaining 9 are all numbers ranging from
0 to 18.
There are NO column headers. All the numeric cells are identically sized.
There are no merged cells.
All the numbers are calculated or derived from elsewhere in the spreadsheet,
i.e all function results or additions of disparate cells. All are valid - no
hashes/errors.

I've tested "sort" on a straightforward set of number columns - it works
fine, on single "direct-keyed" nuimbers !

HELP please.

Len

Len

The usual problem with bad sorting of numerical data is that some of the data
is Numerical and some is Text. The two sort differently.

What looks like a Number may be Text.

Try copying an empty cell then select your range of "numbers" and Paste
SpecialAddOKEsc.

This operation should change all "text numbers" to real numbers.

Try your sort again.

Gord Dibben XL2002

On Sun, 26 Oct 2003 00:32:10 +0100, "Len Dolby"
wrote:

I have problem with a small database - it will not sort.
That is, it DOES change the data around, but not in any comprehensible way.
Having selected the database block, I requested "sort" on last column,
expecting to see a descending numeric order. I got garbage.
I cannot determine exactly what the order is, but it's certainly neither
numeric nor alpha. At least the "garbage" will "Undo" (reverts to original)
Nor is it related to the underlying reference cells which yield the number
Data Sort option is (correctly) set to sort "top to bottom" in descending
order - expected result, the data re-presented with last column in decending
order from 18 to 0.
.
Construction - 11 columns x 39 rows. No blank columns, rows or cells..
1st 2 columns are text (names), the remaining 9 are all numbers ranging from
0 to 18.
There are NO column headers. All the numeric cells are identically sized.
There are no merged cells.
All the numbers are calculated or derived from elsewhere in the spreadsheet,
i.e all function results or additions of disparate cells. All are valid - no
hashes/errors.

I've tested "sort" on a straightforward set of number columns - it works
fine, on single "direct-keyed" nuimbers !

HELP please.

Len

Yes, it works - thanks ! But what a pain to have to redo the database ...

Whilst the spreadsheet itself includes individual data cells with "alpha"
text numbers, these are subsequently counted numerically - or so I thought.
It is these apparently numeric "count" cells which are transferred to the
database, not the original datacell(s)

=SUMPRODUCT(ISTEXT(E5:Y5)*(E5:Y5="1")) is a function used in cell AB5. (The
visible answer is 2). It is counting the instances of " '1") in data range
E5:Y5.
The answer 2 is transported to the database cell via "=$AB5+a series of
similar "count" cells in col $AB with same SUMPRODUCT function - the
database cell shows 10, the product of the "=$AB5+series..

Am copying to Tom (who supplied the function) as he may shed some light on
why what is apparently a numeric count is in fact text.
TOM - any idea why this count in SUMPRODUCT apparently yields a text number
? Better yet, how to systematically (programatically) restore the count to
numeric ?

Sincerely, Len




<Gord Dibben wrote in message
...
Len

The usual problem with bad sorting of numerical data is that some of the
data
is Numerical and some is Text. The two sort differently.

What looks like a Number may be Text.

Try copying an empty cell then select your range of "numbers" and Paste
SpecialAddOKEsc.

This operation should change all "text numbers" to real numbers.

Try your sort again.

Gord Dibben XL2002

On Sun, 26 Oct 2003 00:32:10 +0100, "Len Dolby"

wrote:

I have problem with a small database - it will not sort.
That is, it DOES change the data around, but not in any comprehensible
way.
Having selected the database block, I requested "sort" on last column,
expecting to see a descending numeric order. I got garbage.
I cannot determine exactly what the order is, but it's certainly neither
numeric nor alpha. At least the "garbage" will "Undo" (reverts to
original)
Nor is it related to the underlying reference cells which yield the
number
Data Sort option is (correctly) set to sort "top to bottom" in descending
order - expected result, the data re-presented with last column in
decending
order from 18 to 0.
.
Construction - 11 columns x 39 rows. No blank columns, rows or cells..
1st 2 columns are text (names), the remaining 9 are all numbers ranging
from
0 to 18.
There are NO column headers. All the numeric cells are identically sized.
There are no merged cells.
All the numbers are calculated or derived from elsewhere in the
spreadsheet,
i.e all function results or additions of disparate cells. All are valid -
no
hashes/errors.

I've tested "sort" on a straightforward set of number columns - it works
fine, on single "direct-keyed" nuimbers !

HELP please.

Len

The formula won't produce a text number.

More confusing is that you say Gordon's suggestion fixes it? His suggestion
will work with constants, but I wouldn't expect a similar effect with
formulas. You can check those cells with the same IsText or IsNumber
formula. Even if I formatted the cells as text, they were producing numeric
data.

I think the formulas you are trying to sort need to have absolute references
(your sample was relative for row)

=$AB$5+$AB$6

for example.

--
Regards,
Tom Ogilvy




Len Dolby wrote in message
...
Yes, it works - thanks ! But what a pain to have to redo the database ...

Whilst the spreadsheet itself includes individual data cells with "alpha"
text numbers, these are subsequently counted numerically - or so I
thought.
It is these apparently numeric "count" cells which are transferred to the
database, not the original datacell(s)

=SUMPRODUCT(ISTEXT(E5:Y5)*(E5:Y5="1")) is a function used in cell AB5.
(The
visible answer is 2). It is counting the instances of " '1") in data range
E5:Y5.
The answer 2 is transported to the database cell via "=$AB5+a series of
similar "count" cells in col $AB with same SUMPRODUCT function - the
database cell shows 10, the product of the "=$AB5+series..

Am copying to Tom (who supplied the function) as he may shed some light on
why what is apparently a numeric count is in fact text.
TOM - any idea why this count in SUMPRODUCT apparently yields a text
number
? Better yet, how to systematically (programatically) restore the count to
numeric ?

Sincerely, Len




<Gord Dibben wrote in message
...
Len

The usual problem with bad sorting of numerical data is that some of the
data
is Numerical and some is Text. The two sort differently.

What looks like a Number may be Text.

Try copying an empty cell then select your range of "numbers" and Paste
SpecialAddOKEsc.

This operation should change all "text numbers" to real numbers.

Try your sort again.

Gord Dibben XL2002

On Sun, 26 Oct 2003 00:32:10 +0100, "Len Dolby"

wrote:

I have problem with a small database - it will not sort.
That is, it DOES change the data around, but not in any comprehensible
way.
Having selected the database block, I requested "sort" on last column,
expecting to see a descending numeric order. I got garbage.
I cannot determine exactly what the order is, but it's certainly
neither
numeric nor alpha. At least the "garbage" will "Undo" (reverts to
original)
Nor is it related to the underlying reference cells which yield the
number
Data Sort option is (correctly) set to sort "top to bottom" in
descending
order - expected result, the data re-presented with last column in
decending
order from 18 to 0.
.
Construction - 11 columns x 39 rows. No blank columns, rows or cells..
1st 2 columns are text (names), the remaining 9 are all numbers ranging
from
0 to 18.
There are NO column headers. All the numeric cells are identically
sized.
There are no merged cells.
All the numbers are calculated or derived from elsewhere in the
spreadsheet,
i.e all function results or additions of disparate cells. All are
valid -
no
hashes/errors.

I've tested "sort" on a straightforward set of number columns - it
works
fine, on single "direct-keyed" nuimbers !

HELP please.

Len