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.

This article was created to answer the following question.

Back to top

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

1. Type the above formula in cell B4.
2. Press and hold CTRL + SHIFT keys simultaneously.
3. Press Enter once.
4. Release all keys.

Copy cell B4 and paste to cells to the right as far as needed.

Back to top

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.

Back to top

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.

Back to top

3. Sort items by adjacent number in every other value vertically

This formula returns data vertically:

Get the Excel file

Sort-names-by-adjacent-numberv2.xlsx

Back to top

Sort values category

Sort a column alphabetically
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]

Extract a unique distinct list sorted from A to Z ignore blanks
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]

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

Excel categories

Comments (7)