# 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:

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

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

### 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))