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. […]
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 […]
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.
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))