Sorting Text Data
 

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

Sorting Text Data
 

You will need to use a macro
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pritesh" wrote in message
...
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

Sorting Text Data
 

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.
--
Gary''s Student - gsnu200902


"Pritesh" wrote:

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

Sorting Text Data
 

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200902


"Pritesh" wrote:

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

Sorting Text Data
 

=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