This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee.
I have to admit, this problem doesn't come up often, but when it does, this is a very slick way to eliminate hours of retyping or copy/pasting.
Suppose you have two columns of data and you want to create a third column of data generated from the first two. The catch is we need to interleave the data from one column into the next.
Column “A” is a list of gemstones. Column “B” is a list of countries.
We need the resulting list to look like this:
In cell C1, type
=INDIRECT("A"&INT(ROW()/2)+1)
In cell C2, type
=INDIRECT("B"&ROW()/2)
After you have these two formulas in place, highlight BOTH formulas and then grab the fill series handle and pull down as far as needed to create a single column containing all of the combined data.
Here’s how it works:
Cell C1 Formula
=INDIRECT("A"&INT(ROW()/2)+1)
Take the current row number, divide it by two, round it down to closest integer, add one, and concatenate an A in front of it.
Cell C2 Formula
=INDIRECT("B"&ROW()/2)
Take the current row number, divide it by two, and concatenate an B in front of it.
The result of both calculations is used by the INDIRECT function as an instruction of what do to; in this case, =A1 (show what is in A1), and =B1 (show what is in B1).
Thanks again, Bryon for writing this tutorial and using color coding to help us follow along. We appreciate you sharing your knowledge.
Bryon is from Bristol, Tennessee and has been teaching Excel since version 7 which was included with Office 95. He is currently a Technical Training Analyst for one of the largest coal companies in the world. His key responsibility is to conduct all Microsoft Office training, but in addition he also serves as a technical consultant for any and all projects involving Microsoft Office applications. Bryon says “Excel is by far my favorite among all the Office applications due to the almost infinite number of situations it can be used. There literally seems to be no end to its usefulness. My favorite Excel tools are difficult to narrow down. Without getting into third party add-ins, I would have to say PivotTables (especially with the addition of Slicers. WOW! Are those things awesome!!), the Text to Columns tool (what a time saver), and Macros.”
Vote for Bryon
If you’d like to vote for Bryon’s tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment to thank Bryon for taking the time to share his knowledge….or all of the above 🙂
Paul Castle says
Doesn’t work if one column is for a date, I’m afraid, and you get ‘40878’ instead of ’01/12/2011′. But I’m sure this’ll come in for something else.
Mynda Treacy says
Hi Paul,
That’s correct, just format the cell containing 40878 as a date and you’ll see it’s 1/12/2011. It’s just that the formula returns the date serial number, which is how Excel stores dates.
You can read more about how Excel handles dates and time here.
Mynda
Roni says
What if I have data like this:
Jan Feb Mar
0.88 0.50 0.39
0.86 0.50 0.36
0.86 0.46 0.39
0.79 0.43 0.39
0.74 0.40 0.32
0.74 0.43 0.35
0.72 0.46 0.35
0.75 0.46 0.36
0.76 0.43 0.38
0.75 0.50 0.42
but want it arranged like this:
Jan
0.88
0.86
0.86
0.79
0.74
0.74
0.72
0.75
0.76
0.75
Feb
0.5
0.5
0.46
0.43
0.4
0.43
0.46
0.46
0.43
0.5
etc.
I get data like this all the time for each month and have to do it manually.
Grateful for any help at all.
Thanks!
Roni
Carlo Estopia says
Hi Roni,
You know what? I don’t have a slick solution for this one.
It could be a two-step solution.
So say hello for me here: HELP DESK ; that is,
if you want the solution anyway.
By the way, the idea is like this:
NOTE:Data is at A1:C7
formula starts in A8
Jan Feb Mar 1 2 3 4 5 6 7 8 9 0 a b d e f =IF(ROW()< =13,INDIRECT("A"&ROW()-7),IF(ROW()<=19,INDIRECT("B"&ROW()-13),IF(ROW()<=25,INDIRECT("C"&ROW()-19))))You can then get the handle and drag as far as necessary.
Cheers,
CarloE
Roni says
Well explained Byron. Thanks for sharing! Understand perfectly.
DJ Sachs says
This solution is sensitive to which row is the first row of data. The example above starts in Row 1. If the starting row isn’t Row 1 (i.e., if Row 1 contains column headings like “Gem” and “Country”), one of the formulas needs to be changed (depending on whether the first row is odd [“A” formula needs to be changed] or even [“B” formula needs to be changed]). Also, the INT function needs to be added to the “B” formula if the first row of data isn’t Row 1.
if Starting Row is: Formula needed
Row 1 "A" formula =INDIRECT("A"&INT(ROW()/2)+1) Row 1 "B" formula =INDIRECT("B"&INT(ROW()/2)) Row 2 "A" formula =INDIRECT("A"&INT(ROW()/2)+1) Row 2 "B" formula =INDIRECT("B"&INT(ROW()/2)+1) Row 3 "A" formula =INDIRECT("A"&INT(ROW()/2)+2) Row 3 "B" formula =INDIRECT("B"&INT(ROW()/2)+1)Mynda Treacy says
True. Thanks for sharing, DJ Sachs 🙂
aaron says
What about if you are starting in row 6?
I get the idea that you need to change one of the two formulas depending on where your data begins. I don’t understand what parts of the formula to change to make it work with the appropriate row.
What are the ‘fixed’ and variable parts of the formula?
PS. this is the only good tutorial on this issue I could find. It was hard to find the correct words to describe and google the problem. Thank you!!
Carlo Estopia says
Dear Aaron,
The variable part is merely the + 1 part. that’s all. simply replace it to get A1 or B1 respectively in the INT FUNCTION PART. Also follow the formula in A when you want to improvise.
NOTE:INT will take away decimal.
For example: if you want to start in 6 and 7. Your formula should be
at row 6 =INDIRECT("A"&INT(ROW()/2-2) and at row 7 = INDIRECT("B"&INT(ROW()/2-2) simple explanation: row6/2 = 3.. how much will you need to get 1? so? -2 . row7/2 = 3.5 Disregard the .5 and you get 3. how much will you need to get1? hence - 2.Detailed analysis:
Put this formula: INDIRECT(“A”&INT(ROW()/2)+1)) in E1
isolate INT(ROW())/2 in G1 NOTE: no +1 and the /2 is outside the INT Function for emphasis
Get the handles and drag the formulas both as far as row 7.
you’ll see at first that it will look like this:
E1 ……………………….. G1
amethyst ———————–.5
diamond ———————— 1
diamond ———————– 1.5
jam ———————–2
jam ———————–2.5
okay ———————–3
okay ———————– 3.5
NOTE: to convert back the values in col G to its actual result. just remove the .5’s (do not round off) and +1.
so….
hence the result in E1
you will notice that G1 is in the multiple of .5’s. Note that INT Function takes away any decimal out of a number.
However, we put outside the /2 out of the INT Function and no + 1. hence if we’re in row 1.
1/2 = .5 … row 2; 2/2 = 1 etc.
Now in E1.. you will notice that only Amethyst is not repeated. The rest are repeated twice.
So the question: Why? If we put back the 2 inside the int function and put back the +1 inside the int function.
It would have looked like this INT(current row(i.e. 1)/2 + 1) = 1 or INT(1.5) = 1.
in row 2, INT(current row(2)/2 +1) = 2 or INT(2) = 2
in row 3, INT(current row(3)/2 +1) = 2.5 or INT(2.5) = 2.. NOTE 2 and 3 are the same results
So if you put at row 1 INdirect(“A” & INT(ROW()/2+1)) in row 1. it simply means =A1 or amethyst
at row 2 Indirect(“A” & INTROW()/2+1)) in row 2. it simply means =A2 or diamond
at row 3 Indirect(“A” & INTROW()/2+1)) in row 2. it simply means =A2 or diamond
etc… as the illustration above shows.
Now going back to your question, To put Amethyst in row 2… you simply ask yourself: What should I do with the formula to get the result of A1. It’s simple just add + 0… going back in the actual values highlighted above. INT(ROW/2+1) at row 2 will result to 1 … so replace + 1 and put 0 and you will get only 1 :A1.
if in row 3, INT(ROW3/2) = 1 (.5 is taken away) ergo it is still + 0 at row 3 INT(ROW/2+0).
if in row 4, INT(ROW4/2) = 2 ergo -1 this time to get A1 INT(ROW()/2-1).
now how about in B? the concept is still simple. to get Australia where you want it. just follow the explanation we have above. Just get INT FUNCTION PART to 1; hence, if you want Australia in row 3… just think: INT(row 3/2) = 1 (.5 taken away) do you still want to add? of course not because you have a 1 already.
I actually have a headache today but this is an interesting topic.
Cheers,
CarloE
saran says
+1 to Bryon.. well explained..
I would solve this prob in different way..
1. Copy Column A in Column C
2. Copy Column B in Column C below A data.
3. Type 1 in Cell D1, 3 in Cell D2 and drag the numbers till A column data ends
4. Type 2 in Cell D17 (above), 4 in Cell D18 and drag the numbers till B column data ends
5. Simply, sort the Column D from smallest to largest by selecting Column C & D
6. Thant’s all !! Column C data is our solution
I explained the solution to the guys, who don’t want to go for formulas.
Please note that this is only alternative way of solving.. Bryon’s indirect formula way is ultimate.
Regards,
Saran
Mynda Treacy says
Cheers, Saran. Great alternative.
saran says
Cheers.. Mynda
Ravi Xavier says
Thank you very much this is very easy to understand indeed.
Mynda Treacy says
Cheers, Ravi 🙂