Stumped on Data Sorting Issue

  • Thread starter Thread starter KCPDX
  • Start date Start date
K

KCPDX

Anyone have any idea how to sort data according to look like this?

a1
a2
b1
b2
1
11
12
21

ect

Any help would be great!

Thanks
Kris
 
One way:

Insert a helper column adjacent to the data, Enter the formula:

=ISTEXT(A1)

and copy down as far as required. Highlight both columns and sort
Descending on the new Column and Ascending on the original Column then
delete the Helper Column.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Unfortunately that didn't work for me, the new helper column says
true, and when i go to sort it out it ends up like this

1
11
12
21
a1
b2

Am i missing something?
 
I started with the following data in Column A

1
b2
21
b1
12
a1
11
a2

The with the Helper Column with the formulas:

1 FALSE
b2 TRUE
21 FALSE
b1 TRUE
12 FALSE
a1 TRUE
11 FALSE
a2 TRUE

Then highlight all the data in both Columns and select: Data > Sort.

Ensure that the "No header row" is selected at the bottom of the dialog box

Then in the "Sort by" dropdown at the top of the dialog box select Column B
and "Descending"

Next in the middle "Then by" dropdown select Column A and "Ascending"

Finally click on OK and delete the Helper Column

I get:

a1
a2
b1
b2
1
11
12
21

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Unfortunately that didn't work for me, the new helper column says
true, and when i go to sort it out it ends up like this

1
11
12
21
a1
b2

Am i missing something?
 
Sandy Mann said:
I started with the following data in Column A

1
b2
21
b1
12
a1
11
a2
Click to expand...

Ambiguous. Were the 1st, 3rd, 5th and 7th rows entered as numbers or
text? Actually, it's clear from the rest of your reply that they were
numbers.
The with the Helper Column with the formulas:

1       FALSE
b2      TRUE
21      FALSE
b1      TRUE
12      FALSE
a1      TRUE
11      FALSE
a2      TRUE ...
Click to expand...
...

OP was unclear, but I'd read this as the OP meant that the ISTEXT
formulas *ALL* returned TRUE, which means the cells that may APPEAR to
contain numbers actually contain text that just looks like numbers.

Better to use ancillary formulas like =COUNT(-A2) in cell B2, then
sort first on column B then on column A. Of course, better still would
be an Excel option allowing the user to choose different collation
sequences, but that's unlikely to ever happen (at least not until
after OpenOffice provides it).
 
Thank you for your insight Harlan, I like the idea of =COUNT(-A2) for
discerning if text is a text representation of a number. Being a very
simple man with little lateral thinking, I tend to see a number and think
"number"

To the Kris:

If you use Harlan's =COUNT(-A1) formula in place of my =ISTEXT(A1) formula,
then select to sort ascending in both Column B then Column A to get the
result you want.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Man i must be stupid ive tried both ways and still no luck, let me go
through the steps to see where im messing up. BTW im using excel 2003
Anywho i setup a row column A with this

1
b2
21
b1
12
a1
11
a2

Then i setup column B with either =COUNT(-A1) or =ISTEXT(A1)

=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)

I highlight A and B column, then select sort, make sure no header row
is selected, then i select sort by B ascending, Then by A ascending
then click ok. And the results are

1 1
11 0
12 0
21 1
a1 0
a2 0
b1 1
b2 0

What am I doing wrong?

Thanks
Kris
 
Hi Kris,

I followed your steps through with the *numbers* both as text
representations of numbers and as true numbers. I assume that when you say:

***************************************************
Then i setup column B with either =COUNT(-A1) or =ISTEXT(A1)

=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
****************************************************

You actually mean that you entered =COUNT(-A1) in B1 and dragged the formula
down on the fill handle so that you ended up with:

=COUNT(-A1)
=COUNT(-A2)
=COUNT(-A3)
=COUNT(-A4)
=COUNT(-A5)
=COUNT(-A6)
=COUNT(-A7)
=COUNT(-A8)

If that is the case then I found that it worked as you want it to sort.

I then re-read what you wrote more carefully and saw that there was another
possible interpretation of:

****************************************************
I highlight A and B column, then select sort, make sure no header row
is selected, then i select sort by B ascending, Then by A ascending
then click ok.
****************************************************

Are you doing, (ie completing), *one* Sort on Column B and *then* selecting
to do *another* Sort on Column A? If so then that is what is wrong and that
way results in the layout that you are reporting. You do *BOTH* Sorts at
the same time. In the sort dialog box there are three dropdown boxes, "Sort
by", "Then by" and a second "Then by". In the "Sort by" box you enter:
Column B and then in the "Then by" box you enter: Column A, then, and only
then, you press OK

I that is what you are already doing then please forgive me for over
simplifying the above.

Post back if you are still having problems.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Man i must be stupid ive tried both ways and still no luck, let me go
through the steps to see where im messing up. BTW im using excel 2003
Anywho i setup a row column A with this

1
b2
21
b1
12
a1
11
a2

Then i setup column B with either =COUNT(-A1) or =ISTEXT(A1)

=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)
=COUNT(-A1)

I highlight A and B column, then select sort, make sure no header row
is selected, then i select sort by B ascending, Then by A ascending
then click ok. And the results are

1 1
11 0
12 0
21 1
a1 0
a2 0
b1 1
b2 0

What am I doing wrong?

Thanks
Kris
 
Create a helper column with the formula =IF(NOT(ISERROR(VALUE(LEFT(A1,1)))),"zzzz","")&A1

And sort on that
 
Back
Top