## Rearrange cells in a cell range to vertically distributed values

*Article last updated on February 09, 2018*

The formula in cell B8 uses a named range to calculate the row and column needed to extract the correct value.

To create a named range simply select the cell range (B3:E5) and click in name box. Type a name for that range, I named the cell range rng. Press Enter, that's it.

### Explaining formula in cell B8

The INDEX function needs a row and column number in order to get the correct value.

The ROWS function returns the number of rows in a cell range.

ROWS(rng) becomes ROWS(B3:E5) and returns 3.

The ROW function returns the row number of a cell reference.

ROW(A1) returns 1.

To calculate the row number I use the MOD function to build a repeating number sequence. There are three rows in B3:E5 so the sequence must be 1,2,3,1,2,3, ... and so on.

MOD(ROW(A1)-1, ROWS(rng))+1

becomes

MOD(1-1, 3)+1

becomes

MOD(0, 3) + 1 and returns 1.

To calculate the column number I use the QUOTIENT function to build a repeating number sequence: 1,1,1,2,2,2,3 ... and so on.

QUOTIENT(ROW(A1)-1, ROWS(rng))+1

becomes

QUOTIENT(1-1, 3)+1

becomes

QUOTIENT(0, 3)+1 and returns 1.

The INDEX function then returns the value in row 1 and column 1 from cell range 3:E5.

INDEX(rng, MOD(ROW(A1)-1, ROWS(rng))+1, QUOTIENT(ROW(A1)-1, ROWS(rng))+1)

becomes

INDEX(rng, 1, 1)

and returns "Veges are us" in cell B8.

### Returning values row by row

The following formula gets the values row by row:

### Download Excel *.xlsx file

Rearrange a cell range to vertically distributed values.xlsx

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Extract all rows from a range that meet criteria in one column

Lookup with criteria and return records.

Filter records that contain numeric values

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo […]

Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I […]

The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]

The ROWS function allows you to calculate the number of rows in a cell range. The example above shows that cell […]

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form