sort dates without using filter

[rnl0085]

i have a table:
02-05-2012 1
02-07-2012 2
01-14-2012 3

Is it possible to sort this to date, without using the filter function that i get back:
3
1
2

Or do I have to use a macro for it with this array as input?

 

[Trouttrap2]

How do you want it sorted? By Date from past to current or by number low to high?

<TABLE style="WIDTH: 122pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=163 border=0 x:str><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=99 height=17>Date</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Number</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40922">1/14/2012
</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40944">2/5/2012
</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR title="Post 2858323" style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40946">2/7/2012</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>2</TD></TR></TBODY></TABLE>

Does your sorting button on your tool bar A-Z work?

 

[Robert Mika]

If you use:
=RANK(A1,$A$1:$A$3)

That will give
2
1
3

Coulld you explain logic behind:
3
1
2

 

[markmzz]

Maybe this (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Date</td><td style="text-align: center;;">Number</td><td style="text-align: center;;"></td><td style="text-align: center;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">2/5/2012</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">2/7/2012</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">1/14/2012</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet6</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">D$2:D2</font>)>COUNT(<font color="Red">A$2:A$4</font>),"",INDEX(<font color="Red">B$2:B$4,MATCH(<font color="Green">SMALL(<font color="Purple">IF(<font color="Teal">A$2:A$4<>"",A$2:A$4,3E+300</font>)-(<font color="Teal">ROW(<font color="#FF00FF">A$2:A$4</font>)/10^6</font>),ROWS(<font color="Teal">D$2:D2</font>)</font>),IF(<font color="Purple">A$2:A$4<>"",A$2:A$4,3E+300</font>)-(<font color="Purple">ROW(<font color="Teal">A$2:A$4</font>)/10^6</font>),0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[rnl0085]

@Robert,

the 3 1 2 logic is just that it is sorted by corresponding date

@ mark

i tried it like that..but it gives me all the same values (3 3 3), but still..this function won't be really dynamic (if the range for example changes)

then the only solution would be using a macro?

 

[markmzz]

@Robert,

the 3 1 2 logic is just that it is sorted by corresponding date

@ mark

i tried it like that..but it gives me all the same values (3 3 3), but still..this function won't be really dynamic (if the range for example changes)

then the only solution would be using a macro?

For dynamic range, try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Date</td><td style="text-align: center;;">Number</td><td style="text-align: center;;"></td><td style="text-align: center;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">2/5/2012</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">2/7/2012</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">1/14/2012</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet61</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">D$2:D2</font>)>COUNT(<font color="Red">myDT</font>),"",INDEX(<font color="Red">myNB,MATCH(<font color="Green">SMALL(<font color="Purple">IF(<font color="Teal">myDT<>"",myDT,3E+300</font>)-(<font color="Teal">ROW(<font color="#FF00FF">myDT</font>)/10^6</font>),ROWS(<font color="Teal">D$2:D2</font>)</font>),IF(<font color="Purple">myDT<>"",myDT,3E+300</font>)-(<font color="Purple">ROW(<font color="Teal">myDT</font>)/10^6</font>),0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">myDT</th><td style="text-align:left">=Sheet61!$A$2:INDEX(<font color="Blue">Sheet61!$A:$A,MATCH(<font color="Red">3E+300,Sheet61!$A:$A,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">myNB</th><td style="text-align:left">=Sheet61!$B$2:INDEX(<font color="Blue">Sheet61!$B:$B,MATCH(<font color="Red">3E+300,Sheet61!$A:$A,1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Did you used Ctrl+Shift+Enter?

Markmzz

 

[rnl0085]

i have tried it now and still gives all values 4 (with 4 dates in it)..i did the crtl shift enter..

when i look in name manager however the myDT and myNB are empty..

 

[markmzz]

i have tried it now and still gives all values 4 (with 4 dates in it)..i did the crtl shift enter..

when i look in name manager however the myDT and myNB are empty..

Rnl0085,

Look at your PM.

Markmzz

 

[markmzz]

More dates and a little small formula:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Date</td><td style="text-align: center;;">Number</td><td style="text-align: center;;"></td><td style="text-align: center;;">Result</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;background-color: #C5D9F1;;">1/1/2012</td><td style="text-align: center;background-color: #FFFF00;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #C5D9F1;;">myDT: =Sheet7!$A$2:INDEX(Sheet7!$A:$A,MATCH(3E+300,Sheet7!$A:$A,1))</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #C5D9F1;;">1/14/2012</td><td style="text-align: center;background-color: #FFFF00;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;background-color: #C5D9F1;;">1/30/2012</td><td style="text-align: center;background-color: #FFFF00;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;">8</td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #FFFF00;;">myNB: =Sheet7!$B$2:INDEX(Sheet7!$B:$B,MATCH(3E+300,Sheet7!$A:$A,1))</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #C5D9F1;;">2/7/2012</td><td style="text-align: center;background-color: #FFFF00;;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;background-color: #C5D9F1;;">2/15/2012</td><td style="text-align: center;background-color: #FFFF00;;">4</td><td style="text-align: right;;"></td><td style="text-align: center;;">4</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Column Date format - m/d/yyyy</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;background-color: #C5D9F1;;">3/2/2012</td><td style="text-align: center;background-color: #FFFF00;;">3</td><td style="text-align: right;;"></td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;background-color: #C5D9F1;;">3/28/2012</td><td style="text-align: center;background-color: #FFFF00;;">2</td><td style="text-align: right;;"></td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;background-color: #C5D9F1;;">4/3/2012</td><td style="text-align: center;background-color: #FFFF00;;">1</td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet7</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">D$2:D2</font>)>COUNT(<font color="Red">myDT</font>),"",INDEX(<font color="Red">myNB,MATCH(<font color="Green">SMALL(<font color="Purple">IF(<font color="Teal">myDT<>"",myDT</font>)-(<font color="Teal">ROW(<font color="#FF00FF">myDT</font>)/10^6</font>),ROWS(<font color="Teal">D$2:D2</font>)</font>),IF(<font color="Purple">myDT<>"",myDT</font>)-(<font color="Purple">ROW(<font color="Teal">myDT</font>)/10^6</font>),0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Sheet7!myDT</th><td style="text-align:left">=Sheet7!$A$2:INDEX(<font color="Blue">Sheet7!$A:$A,MATCH(<font color="Red">3E+300,Sheet7!$A:$A,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Sheet7!myNB</th><td style="text-align:left">=Sheet7!$B$2:INDEX(<font color="Blue">Sheet7!$B:$B,MATCH(<font color="Red">3E+300,Sheet7!$A:$A,1</font>)</font>)</td></tr></tbody></table></td></tr></table>
Markmzz

 

[rnl0085]

but the numbers is the second column should be from 1 to 7..