sorting data
 

hi i have the following which sorts my data but would like it to only
include unique records. can and how would i do this?

INDEX($A$1:$A$6,MATCH(SMALL(IF($A$1:$A$6="","",COU NTIF($A$1:$A$6,"<"&$A$1:$
A$6)),ROW(A1)),IF($A$1:$A$6="","",COUNTIF($A$1:$A$ 6,"<"&$A$1:$A$6)),0))


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=392116

If you want to create a list of unique items, look at...

DataFilterAdvanced FilterCopy to another locationUnique Records Only

HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"ceemo" wrote:


hi i have the following which sorts my data but would like it to only
include unique records. can and how would i do this?

INDEX($A$1:$A$6,MATCH(SMALL(IF($A$1:$A$6="","",COU NTIF($A$1:$A$6,"<"&$A$1:$
A$6)),ROW(A1)),IF($A$1:$A$6="","",COUNTIF($A$1:$A$ 6,"<"&$A$1:$A$6)),0))


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=392116

Try...

B1, copied down:

=INDEX($A$1:$A$6,MATCH(SMALL(IF(($A$1:$A$6<"")*(C OUNTIF(OFFSET($A$1,0,0,ROW($A$1:$A$6)-ROW($A$1)+1),$A$1:$A$6)=1),COUNTIF($A$1:$A$6,"<"&$ A$1:$A$6)),ROWS($B$1:B1)),COUNTIF($A$1:$A$6,"<"&$A $1:$A$6),0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this heps!

ceemo Wrote:
hi i have the following which sorts my data but would like it to only
include unique records. can and how would i do this?

INDEX($A$1:$A$6,MATCH(SMALL(IF($A$1:$A$6="","",COU NTIF($A$1:$A$6,"<"&$A$1:$
A$6)),ROW(A1)),IF($A$1:$A$6="","",COUNTIF($A$1:$A$ 6,"<"&$A$1:$A$6)),0))


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=392116

"ceemo" ha scritto nel
messaggio ...

hi i have the following which sorts my data but would like it to only
include unique records. can and how would i do this?


INDEX($A$1:$A$6,MATCH(SMALL(IF($A$1:$A$6="","",COU NTIF($A$1:$A$6,"<"&$A$1:$
A$6)),ROW(A1)),IF($A$1:$A$6="","",COUNTIF($A$1:$A$ 6,"<"&$A$1:$A$6)),0))

hy,
try to:
=INDEX(text,MATCH(SMALL(IF(MATCH(text,text,)=ROW(t ext),1)*COUNTIF(text,"<="&
text),ROW(text)+ROWS(text)-SUM(1/COUNTIF(text,text))),COUNTIF(text,"<="&text
),))

text is your range
you must enter this formula as array fomrulas in a range.

ivano