Sort names by value
I have the following situation:
A1, B1, C1, D1, E1, F1
where
A1 = nick
b1 = 10
c1 = zack
d1 = 15
e1 - john
f1 = 13
what formula should i use to get them ordered counting the numbers but names still being asociated, like this:
a1 = zack
b1 = 15
c1 = john
d1 = 13
e1 = nick
f1 = 10
Formula in cell A3:
Explaining formula
Find n-th largest number in cell range and repeat every number
ROUND(COLUMN(A1)*0.5,0)
becomes ROUND(1*0.5,0)
becomes ROUND(0.5,0)
and returns 1. Copy the formula to the next cell gives ROUND(COLUMN(B1)*0.5,0) and returns 1 again.
As you copy the formula to the left this sequence is created 1,1,2,2,3,3,4,4,5,5 and so on.
What happens if we combine the above with the LARGE function?
LARGE($A$1:$F$1,ROUND(COLUMN(A1)*0.5,0))
The LARGE function extracts the n-th largest number twice for every two cells. This allows us to return both the number and the corresponding name later on.
Find position of the n-th largest number in cell range
This formula is valid for cell A3, when you copy it to cell A4 the cell refs change. Check out blog post absolute and relative cell references and learn how to build smarter formulas.
MATCH(LARGE($A$1:$F$1,ROUND(COLUMN(A1)*0.5,0)),$A$1:$F$1,0)
becomes
MATCH(15,$A$1:$F$1,0)
and returns 4. Read more about MATCH function.
Subtract one to get the corresponding name for every second cell.
MATCH(LARGE($A$1:$F$1,ROUND(COLUMN(A1)*0.5,0)),$A$1:$F$1,0)-MOD(COLUMN(A1),2)
MOD(COLUMN(A1),2) returns 1,0,1,0,1, ... and so on as you copy the formula further to the right. This enables the formula to extract the name for every second cell.
More MOD examples: Learn how the MOD function works, Quotient, Mod and Int functions
Return number or name
=INDEX($A$1:$F$1,MATCH(LARGE($A$1:$F$1,ROUND(COLUMN(A1)*0.5,0)),$A$1:$F$1,0)-MOD(COLUMN(A1),2))
The INDEX function returns n-th largest number or corresponding name.
Data vertically
This formula returns data vertically:
Download excel *.xlsx
Sort names by adjacent number.xlsx
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo […]
Can someone explain to me why this happens? This example is working. FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and […]
The quotient function returns the integer portion of a division. Example, 5/2 = 2.5. The integer is 2. Excel Function […]
Rearrange cells in a cell range to vertically distributed values
The formula in cell B8 uses a named range to calculate the row and column needed to extract the correct […]
Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]
Create a unique distinct sorted list containing both numbers text removing blanks
The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]
Sort dates within a date range
Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]
The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]
Lookup and return multiple values sorted in a custom order
Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]
7 Responses to “Sort names by value”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Elegant solution!
Excellent job Oscar, and not for the first time.
I like your site.
Beautiful formula,
you're not even an array.
Thank you all!
David, you are right. You don't need to enter the formula as an array formula. I was wrong. Thanks for pointing that out.
By the way, substitute the LARGE function with the SMALL function to sort values in ascending order.
[…] ← Previous post - […]
By adding a small value like COLUMN($A$1:$F$1)/10000 we can handle ties(not array formula entered in A3):
=INDEX($A$1:$F$1,MATCH(AGGREGATE(14,6, $A$1:$F$1+COLUMN($A$1:$F$1)/10000,ROUND(COLUMN(A1)*0.5,0)),INDEX($A$1:$F$1+COLUMN($A$1:$F$1)/10000,,),0)-MOD(COLUMN(A1),2))