alphabetise

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ignus99 - 02 Jun 2005 18:28 GMT

Sorry for the newb question. but is there a way to have on column the
alphebetised version of another column? thanks in advance

Signature

ignus99

Reply to this Message

Domenic - 02 Jun 2005 19:35 GMT

Assuming that A1:A10 contains your data, try the following...

B1, copied down:

=COUNTIF($A$1:$A$10,"<"&A1)+1

C1, copied down:

=INDEX($A$1:$A$10,MATCH(ROW()-ROW($C$1)+1,$B$1:$B$10,0))

Hope this helps!

Signature

Domenic

Reply to this Message

Bob Phillips - 02 Jun 2005 22:08 GMT

Nice Domenic, but seems to throw a wobbly with repeated data

Bob

> Assuming that A1:A10 contains your data, try the following...
>
[quoted text clipped - 7 lines]
>
> Hope this helps!

Reply to this Message

Domenic - 02 Jun 2005 22:37 GMT

Actually, with the data containing text values, I hadn't thought about
repeated data. In this case, the formula for B1 could be changed to the
following...

=(COUNTIF($A$1:$A$10,"<"&A1)+1)+(COUNTIF($A$1:A1,A1)-1)

Thanks for catching that Bob! Much appreciated! :)

> Nice Domenic, but seems to throw a wobbly with repeated data
>
[quoted text clipped - 14 lines]
> http://www.excelforum.com/member.php?action=getinfo&userid=10785
> > View this thread: http://www.excelforum.com/showthread.php?threadid=376053

Reply to this Message

Bob Phillips - 03 Jun 2005 00:01 GMT

I always like a nice solution :-).

Regards

Bob

> Actually, with the data containing text values, I hadn't thought about
> repeated data. In this case, the formula for B1 could be changed to the
[quoted text clipped - 24 lines]
> > http://www.excelforum.com/member.php?action=getinfo&userid=10785
> > > View this thread: http://www.excelforum.com/showthread.php?threadid=376053

Reply to this Message

Ken Wright - 02 Jun 2005 19:55 GMT

No apologies needed regardless of the level of the question, but you may
have to clarify exactly what you mean, as personally I'm a tad confused to
be honest.

Signature

Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

> Sorry for the newb question. but is there a way to have on column the
> alphebetised version of another column? thanks in advance

Reply to this Message