Unique values

[LiAD]

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)

 

[Ron Rosenfeld]

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

 

[Eduardo]

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

 

[Max]

.. 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
---

 

[Ashish Mathur]

Hi,

Download and install the following add-in
http://www.download.com/Morefunc/3000-2077_4-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