Sort items by adjacent number in every other value
This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a number.
The image above shows the data in cell range B2:G2 and the array formula in cell B4 sorts the items based on the adjacent number from large to small.
Having data arranged like the image above shows is not something I recommend, it is far better to have the items in one column and the corresponding numbers in the next column.
You can then sort data much easier using the built-in Filter tool or convert the data to an Excel Table which gives you a lot of extra features like formatting and so on.
What's on this page
This article was created to answer the following question.
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 associated, like this:
a1 = zack
b1 = 15
c1 = john
d1 = 13
e1 = nick
f1 = 10
1. Sort items by adjacent number in every other value - Array formula
Array formula in cell B4:
1.1 How to enter an array formula
- Type the above formula in cell B4.
- Press and hold CTRL + SHIFT keys simultaneously.
- Press Enter once.
- Release all keys.
Copy cell B4 and paste to cells to the right as far as needed.
1.2 Explaining formula in cell B4
Step 1 - Calculate column number
The COLUMN function returns the column number of the top-left cell of a cell reference.
COLUMN(reference)
COLUMN(A1)*0.5
becomes
1*0.5
The asterisk character is a mathematical operator, it multiplies two numbers.
1*0.5 equals 0.5.
Step 2 - Round number to nearest integer
The ROUND function rounds a number based on the number of digits you specify.
ROUND(number, num_digits)
ROUND(COLUMN(A1)*0.5,0)
becomes
ROUNDD(0.5)
and returns 1.
Step 3 - Extract k-th largest number
The LARGE function calculates the k-th largest value from an array of numbers.
LARGE(array, k)
LARGE($B$2:$G$2, ROUND(COLUMN(A1)*0.5, 0))
becomes
LARGE($B$2:$G$2, 1)
becomes
LARGE({"Nick", 10, "Zack", 15, "John", 13}, 1)
and returns 15. 15 is the largest number in the array.
Step 4 - Find relative position
The MATCH function returns the relative position of an item in an array or cell reference.
MATCH(lookup_value, lookup_array, [match_type])
MATCH(LARGE($B$2:$G$2,ROUND(COLUMN(A1)*0.5,0)),$B$2:$G$2,0)
becomes
MATCH(15, $B$2:$G$2,0)
becomes
MATCH(15, {"Nick", 10, "Zack", 15, "John", 13},0)
and returns 4. 15 is the fourth value in the array.
Step 5 - Calculate a number sequence that alternates between 0 (zero) and 1.
The MOD function returns the remainder after a number is divided by a divisor.
MOD(number, divisor)
MOD(COLUMN(A1),2)
becomes
MOD(1,2)
and returns 1.
Step 6 - Calculate position
MATCH(LARGE($B$2:$G$2,ROUND(COLUMN(A1)*0.5,0)),$B$2:$G$2,0)-MOD(COLUMN(A1))
becomes
4-MOD(COLUMN(A1))
becomes
4-1
and returns 3.
Step 7 - Get value
The INDEX function returns a value from a cell range based on a row and column number.
INDEX(array, [row_num], [column_num])
INDEX($B$2:$G$2,MATCH(LARGE($B$2:$G$2,ROUND(COLUMN(A1)*0.5,0)),$B$2:$G$2,0)-MOD(COLUMN(A1),2))
becomes
INDEX($B$2:$G$2,3)
becomes
INDEX({"Nick", 10, "Zack", 15, "John", 13},3)
and returns "Zack" in cell B4.
2. Sort items by adjacent number in every other value - Excel 365 formula
The image above demonstrates a dynamic array formula in cell B4 that sorts and rearranges values based on a horizontal cell range.
Excel 365 formula in cell B4:
The formula above is entered as a regular formula and it works only in Excel 365.
3. Sort items by adjacent number in every other value vertically
This formula returns data vertically:
Sort values category
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]
Excel categories
7 Responses to “Sort items by adjacent number in every other 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.
Contact Oscar
You can contact me through this contact form
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))