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
In cell C2, type
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
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
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 🙂