Sorting Text Data

[Pritesh]

Hi,

I need to prepare a template where, user copies data in one sheet, out of
which in another sheet, I need a particular one column gets
automatically-sorted alphabetically (without user using "Sort Function").
Just like using LARGE formula for digital data.

I am using Excel 2007. I am avoiding use of any Macros for this.

Pleasse help.


Regards,
Pritesh

 

[Don Guillett]

You will need to use a macro

 

[Gary''s Student]

You can alphbetize by formula. Say in A1 thru A10 we have:

cat
dog
mouse
bat
hat
rat
fat
mat
zort
tre

In B1 enter the array formula:

=IF(ROWS(A$1:A1)<=COUNTA($A$1:$A$100),INDEX($A$1:$A$100,MATCH(SMALL(IF($A$1:$A$100<>"",COUNTIF($A$1:$A$100,"<"&$A$1:$A$100)),ROWS(A$1:A1)),IF($A$1:$A$100<>"",COUNTIF($A$1:$A$100,"<"&$A$1:$A$100)),0)),"")

You must enter this with CNTRL-SHFT-ENTER rather than just the ENTER key.

Then copy B1 down to B10. In B1 thru B10 we now see:

bat
cat
dog
fat
hat
mat
mouse
rat
tre
zort

Adjust the $A$100 if you have more than 100 items to sort.

 

[Pritesh]

Hi,

You have used so many formulas within this-one, so I can't really understand
how, BUT IT APPEARS TO BE WORKING. So Thanks for sending it & helping.

Regards,
Pritesh

 

[Bernd P]

=IF(ROWS(A$1:A1)<=COUNTA($A$1:$A$100),INDEX($A$1:$A$100,MATCH(SMALL(IF($A$1­:$A$100<>"",COUNTIF($A$1:$A$100,"<"&$A$1:$A$100)),ROWS(A$1:A1)),IF($A$1:$A$­100<>"",COUNTIF($A$1:$A$100,"<"&$A$1:$A$100)),0)),"")

Hello,

That formula does not work for numbers and not for "text numbers"
different from '1.

It is not necessary to use an array formula if you are willing to use
a helper column:
http://sulprobil.com/html/sorting.html

Regards,
Bernd