Numerical order of cells

[jswillcox]

I have a question about automatically ordering cells.

I have cells that show as follows

A B
1 |Cinthia 12
2 |Scot 20
3 |Mike 5


I would like to make it so these cells automatically order by highest to lowest. This number changes daily and I have been ordering them using Sort Highest to lowest. The issue is I need to do that manually every day. How can I have excel order a select number of cells keeping cells a and b together.

How I would like it to order this example

A B
1 |Scot 20
2 |Cinthia 12
3 |Mike 5

I appreciate all your help!

 

[Eric W]

I can think of 2 possible ways to do this.

First, you could create a Worksheet_Change event. It would monitor Column B, and any time a change was made, it would automatically sort columns A:B. I'm not sure how well that would work. It would depend on how your columns are populated. If you try to enter data and it's continually sorting, that could be an issue.

Second way would be to create 2 additional columns with formulas that automatically create a sorted list. For example:

	A	B	C	D	E
Cinthia	Scot
Scot	Cinthia
Mike	Joe
Joe	Mike

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"]20[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]=IFERROR(LARGE($B$1:$B$10,ROWS($E$1:$E1)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]{=IF(E1="","",INDEX(A:A,SMALL(IF($B$1:$B$10=E1,ROW($B$1:$B$10)),COUNTIF($E$1:$E1,E1))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



The new columns are D and E. Enter the E1 formula first, then the D1 array formula. Change the ranges to match the maximum number of rows in your range. Copy down as far as needed.

Hope this helps.