Sorting complex data

[Mark]

Thanks for your help everyone. Im getting there although as I solve
one problem I create another. This is ultimately an attempt to sort an
excel spreadsheet where one of the columns can be blank, numeric,
alphanumeric and numericalpha e.g.

<blank>
<blank>
A1
G1
G4
1
1a
2
5
20
20a
20b
21
21a
999
999b

This is in fact the order I would want (blanks first, alpha numerics
sorted next, numerics and numeric alphas following with 1a in between
1 and 2, and 20b following 20a).

I have been using ISNUMBER and ISTEXT to try and split off details
into other sort columns. If anybody is genius enough to take this
list, mix it up and have some kind of function that will resort it, I
will be grateful.


One thing to be aware of. Whatever the solution, I have 2 other
columns which will be used in my final sort so if your solution
creates more than one work sort column, I will not be able to sort
using the standard excel data sort option, as it works on 3 columns.
So if you can tell me how to do this as well (sort on 4 columns) I
would also be grateful

 

[A.W.J. Ales]

Mark,

Add a second column say B (assuming your data is in column A1:A16)
In cell B1 =IF(ISBLANK(A1),1,IF(ISNUMBER(A1),2,IF(ISTEXT(A1),3,4))) and
copy down

Then Data / Sort / Sort by column B and (second) Sort by column A

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[A.W.J. Ales]

Mark,
Sorry formula in previous posting won't work.

Formula should be :
=IF(ISBLANK($A1),1,IF(ISNUMBER($A1),3,IF(ISERROR(VALUE(LEFT(A1))),2,4)))


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Bob Phillips]

Hi Auk,

I think it is more complex than that as the numeric/numeric-alphas are
inter-mixed. I came up with this formula

=IF(ISBLANK(A1),0,IF(CODE(LEFT(A1,1))>60,1,2&IF(CODE(RIGHT(A1,1))>60,LEFT("
",6-LEN(A1))&A1,LEFT(" ",5-LEN(A1))&A1&" ")))

that apart, it's the same approach

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

Also answered in other thread. I came up with the following:-

1 helper column

With your data in Col A, header in A1, data starting A2, in B1 put a header and
then in B2 put

=ISNUMBER(-LEFT(A2))&IF(A2="","",TEXT(A2,"@"))

Copy down as far as necessary, select all data and sort in Ascending order on
Col B

 

[A.W.J. Ales]

Hi Bob,

I don't know on which of my two postings you reacted.
If you did on the first one you are right, but in the second one I corrected
by making a difference between the two (alfa -numeric numeric-alfa) with
help of the "iserror(value(left" combination.
If the first character is a..z or A..Z then Value(Left( generates an error
and the ISERRO generates TRUE.
If however it is 1...9 Value generates no error and the outcome of the
ISERROR thus becomes FALSE.

I did look at and test your solution as well and it works indeed. As you
said it's more or less the same approach.

(Actually I was - in the second posting - trying to make a difference
between a 1..9 and A ... Z on the left and first characters as well.
Because I overlooked the possibility of checking by a CODE larger or smaller
than 60 and ISNUMBER(LEFT( )) in both cases came up with FALSE, I decided to
test on the VALUE).

So even if I did already correct my first solution, I became pointed to the
CODE function again by you reaction.
Keeps you sharp that answering questions isn't it


BTW I hope we didn't lose the OP in this conversation <bg>

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Bob Phillips]

Ken,

Still doesn't meet the OP's criteria, yours goes

<blank>,<blank>,A1,G1,G4,1,1a,2,20,20a,20b,21,21a,5,999,999b

<blank>,<blank>,A1,G1,G4,1,1a,2,5,20,20a,20b,21,21a,999,999b

I really think you need a nasty such as I gave, but which depends upon only
1 alpha.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

<grrrrrrr> Didn't spot that one :-(

 

[A.W.J. Ales]

Hi again Bob,

It's keeping me sharp ???
By using the CODE function and the fact that there are ony four groups I
think that the next formula is easier :
=IF(ISBLANK(A1),0,IF(CODE(LEFT(A1,1))>60,1,IF(ISNUMBER(A1),2,3)))

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Bob Phillips]

Hi Auk,

It was the latter, they were both there by the time I got around to them. As
I read it, the OP wanted the order as

<blank>,<blank>,A1,G1,G4,1,1a,2,5,20,20a,20b,21,21a,999,999b

<blank>,<blank>,A1,G1,G4,1,2,5,20,21,999,1a,20a,20b,21a,999b

It's that requirement to have 1a after 1 but before 2 etc. that is so odd.

It does keep you sharp, but some of the questions are so odd. Why would he
want this order anyway?

As to the OP, I guess he hasn't come back yet, but as Harlan says, who said
we were doing this for the OP, we are trying to learn as well. I am still
hoping that there is a neater solution that I gave. Yours is more elegant,
but I think in this case, that it misses that oddity.

Regards

Bob

 

[A.W.J. Ales]

Hi again Bob,

Now you've got puzzling me Bob.

It I read the OP back, I agree with what you say and I did misread it.
I thought he wanted a sort "by order" [ Blank ; Alfa-Numeric ( letter
followed by number) ; Numeric ; Numeric-ALFA (number followed by
etter) ] and futher sorted within these categories.
Actually I think he is saying two different things. he specifies groups
( see above and says he wants that order) and on the other hand he says he
want 1a between 1 and 2 which is something else. Both demands can't be
satisfied in the same ordering.

I suppose I 'd better wait till the OP specifies more clearly what he
actually does want.

As far as my puzzling is concerned : Your solution gives the same result as
my second and third solution.
It orders like :
<blank>, <blank>, A1, G1, G4, 1, 2, 20, 21, 50, 999, 1a, 20a, 20b, 21a,
999b.

At least if you do the same as I did in my posting. Putting the formula in
a second column and then sorting on that second column and on the first
column thereafter. You made me doubt and therefore I did test it again
( i had allready done once) but I came to the same result.
I don't suppose that this is a matter of an English (mine) XL version and
another version so I wonder.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[A.W.J. Ales]

Hi Bob,

Coming back on the previous posting :

I've found that the fact that your solution gave the same result probably
was caused by the way in your posting the formula was broken over two lines.
=IF(ISBLANK(A1),0,IF(CODE(LEFT(A1,1))>60,1,2&IF(CODE(RIGHT(A1,1))>60,LEFT("
",6-LEN(A1))&A1,LEFT(" ",5-LEN(A1))&A1&" ")))

I assumed (twice !!! ; how sharp <vbg> ) no spaces at the breakpoint thus
"".
Putting one or more spaces *does* make a difference. Up to now I can't
however find the correct number of spaces should do ( I'm now allready up to
8).
No one of them seems to give the result of :
<blank>,<blank>,A1,G1,G4,1,1a,2,5,20,20a,20b,21,21a,999,999b

So : Could you tell me howmany you did intend ?
--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Bob Phillips]

Hi Auk,

The clue is in the statements 6-LEN(A1) and 5-LEN(A1), so the first is 6
spaces, the second 5 spaces. I originally used 10, and then came down some.
This is my nasty frig to try and get the order shown. When there is an
example that disagrees with the words, I tend to go with the example, we all
know how easy to confuse things in text.

Anyway, as you say, you, Ken and I all took the same approach, so it's now
up to the OP to read it and come back or not as to what he actually wants.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mark]

Thanks for all your help chaps. Just read the threads and need to go
away antry out some of your ideas. I'll get back to you if they are
successful

Mark