Unique sorted list

So...

01/11/2010
01/11/2010
01/11/2010
01/09/2010
01/08/2010
08/09/2010

Would become :

01/08/2010
01/09/2010
08/09/2010
01/11/2010

Also would like it to ignore blanks, and only apply it to dates

Hello Theta,

I got this formula from the Excelisfun videos on youtube by Mike Girvin.


First you need to create the following formula in the name manager

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$100000,MATCH(9999999,Sheet1!$A$2:$A$100000))

Click to expand...

This formula is named "datelist"

Then in column B entre the following formula:

{=IFERROR(INDEX(DateList,MATCH(0,COUNTIF(DateList,"<"&DateList)-SUM(COUNTIF(DateList,$B$1:B1)),0)),"")}

Click to expand...

Cntl + Shift +Enter

then you can drag this down and you will have a unique list that is sorted.

Hope this helps you.

theta said:

So...

01/11/2010
01/11/2010
01/11/2010
01/09/2010
01/08/2010
08/09/2010

Would become :

01/08/2010
01/09/2010
08/09/2010
01/11/2010

Also would like it to ignore blanks, and only apply it to dates

Click to expand...

<TABLE style="WIDTH: 191pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=255><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3669" width=103><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3128" width=88><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=103>Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2605099 class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=88>Distinct Dates</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>1-Nov-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>1-Aug-10</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>1-Nov-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>1-Sep-10</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>1-Nov-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>8-Sep-10</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>1-Sep-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>1-Nov-10</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>1-Aug-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=middle>#NUM!</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>8-Sep-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR></TBODY></TABLE>

C2, just enter and copy down as far as needed:

=SMALL($A$2:$A$7,1+SUMPRODUCT(COUNTIF($A$2:$A$7,$C$1:C1)))

That is a a brilliant formula Aladin.

Thanks guys. All great

Just out of curiosity, i'm sure i've seen a version somewhere using

INDEX MATCH SMALL using ROW

Any ideas, annoying me that I can't find it.

Although must say as well, Aladin your formula is amazing. Using SUMPRODUCT to skip the need for an array entered formula

Would still like the INDEX MATCH SMALL and ROW method just for learning etc

maybe in b2 =IF(A2="","",RANK(A2,$A$2:$A$7,1))
in c2 =IF(ROW(A1)>MAX($B$2:$B$7),"",INDEX($A$2:$A$7,MATCH(SMALL($B$2:$B$7,ROW(A1)),$B$2:$B$7,0)))

Hmmm yes seems to work great

Any other suggestions of these methods guys?

Will be producing a workbook with VLOOKUP, LOOKUP, SUMPRODUCT, INDEX/MATCH, FREQUENCY methods etc and uploading it to MrExcel for everybody to use as reference.

Been scouring the site for bets examples

INDEX / MATCH methods always seem to be the trickiest, but also very clever

What about MULTI CONDITION INDEX / MATCH, sorted

So...where the Range 1 = TRUE, give me a unique ranked list from Range 2 (basically this example, but with a TRUE/FALSE column)

There are some methods outlined in the post below

http://www.mrexcel.com/forum/showthread.php?t=518138