Unique values
 

Hi,

Sorry I have already asked this question but the answers were given in VBA,
which I would rather avoid using if possible. Is there any way of sorting a
vertical list of data such as B, A, B, A, C, D, C into A, B, C, D using only
a formula?

I have this formula which I use to delete the empty cells from a list which
I have been trying to adapt to do this but without any luck. Not sure if its
any use for anyone to answer the question.

Thanks for your help

=INDEX($X$2:$X$5000;SMALL(IF($X$2:$X$5000<"";ROW( INDIRECT("1:"&ROWS($X$2:$X$5000))));ROWS($1:1));0)

Unique values
 

On Wed, 22 Jul 2009 02:10:02 -0700, LiAD
wrote:

Hi,

Sorry I have already asked this question but the answers were given in VBA,
which I would rather avoid using if possible. Is there any way of sorting a
vertical list of data such as B, A, B, A, C, D, C into A, B, C, D using only
a formula?

I have this formula which I use to delete the empty cells from a list which
I have been trying to adapt to do this but without any luck. Not sure if its
any use for anyone to answer the question.

Thanks for your help

=INDEX($X$2:$X$5000;SMALL(IF($X$2:$X$5000<"";ROW (INDIRECT("1:"&ROWS($X$2:$X$5000))));ROWS($1:1));0 )

You could use the Data/Sort and then the Advanced Data Filter wizards from the
main tool bar to extract the Uniques in sorted order.
--ron

Unique values
 

Hi,

I supposed your list starts in cell A2
In the adjacent column enter the formula as follow


=IF(COUNTIF($a$2:a2,a2)=1,a2,"")

Then sort it in alphabetical order

"LiAD" wrote:

Hi,

Sorry I have already asked this question but the answers were given in VBA,
which I would rather avoid using if possible. Is there any way of sorting a
vertical list of data such as B, A, B, A, C, D, C into A, B, C, D using only
a formula?

I have this formula which I use to delete the empty cells from a list which
I have been trying to adapt to do this but without any luck. Not sure if its
any use for anyone to answer the question.

Thanks for your help

=INDEX($X$2:$X$5000;SMALL(IF($X$2:$X$5000<"";ROW( INDIRECT("1:"&ROWS($X$2:$X$5000))));ROWS($1:1));0)

Unique values
 

.. sorting a vertical list of data such as B, A, B, A, C, D, C
into A, B, C, D using only a formula?

One formulas tinker which delivers ...
Your source data as posted assumed running in A1 down
In B1:
=IF(A1="","",IF(COUNTIF(A$1:A1,A1)1,"",CODE(LEFT( A1))+ROW()/10^10))

In C1:
=IF(ROWS($1:1)COUNT(B:B),"",INDEX(A:A,MATCH(SMALL (B:B,ROWS($1:1)),B:B,0)))
Copy B1:C1 down to cover the max expected extent of source data.
Minimize/hide col B. Col C will dynamically return the required uniques,
sorted in alpha order (by the 1st/leftmost letter)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"LiAD" wrote:
Sorry I have already asked this question but the answers were given in VBA,
which I would rather avoid using if possible. Is there any way of sorting a
vertical list of data such as B, A, B, A, C, D, C into A, B, C, D using only
a formula?

I have this formula which I use to delete the empty cells from a list which
I have been trying to adapt to do this but without any luck. Not sure if its
any use for anyone to answer the question.

Thanks for your help

=INDEX($X$2:$X$5000;SMALL(IF($X$2:$X$5000<"";ROW( INDIRECT("1:"&ROWS($X$2:$X$5000))));ROWS($1:1));0)

Unique values
 

Hi,

Download and install the following add-in
http://www.download.com/Morefunc/300...-10423159.html and the use the
multi cell UNIQUEVALUES() array formula. Therefore, if the range is in
C6:C11, select range E6:E11 and use the UNIQUEVALUES(C6:C11,1) array
formula.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"LiAD" wrote in message
...
Hi,

Sorry I have already asked this question but the answers were given in
VBA,
which I would rather avoid using if possible. Is there any way of sorting
a
vertical list of data such as B, A, B, A, C, D, C into A, B, C, D using
only
a formula?

I have this formula which I use to delete the empty cells from a list
which
I have been trying to adapt to do this but without any luck. Not sure if
its
any use for anyone to answer the question.

Thanks for your help

=INDEX($X$2:$X$5000;SMALL(IF($X$2:$X$5000<"";ROW( INDIRECT("1:"&ROWS($X$2:$X$5000))));ROWS($1:1));0)