Rearrange cells in a cell range to vertically distributed values
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
Related articles
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 [โฆ]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to [โฆ]
Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there [โฆ]
Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with [โฆ]
Identify all characters in a cell value
Sometimes when you sort values you get unexpected results, the cause is probably unwanted characters in a cell. The same [โฆ]
How to use the COLUMN function
The COLUMN function returns the column number of the top left cell of a cell reference. If the argument is not [โฆ]
Search values distributed horizontally and return corresponding value
Question: Hi, The formula here works great but I can't figure out how to change it to work with data [โฆ]
Filter shared records from two tables
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in [โฆ]
Gets a value in a specific cell range based on a row and column number.
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 [โฆ]
Can someone explain to me why this happens? This example is working. FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and [โฆ]
The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo [โฆ]
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 [โฆ]
Count overlapping days in multiple date ranges
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges [โฆ]
The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference [โฆ]
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
Paste image link to your comment.
English
Spanish
