Our task is to repeat each original row three times and then cascade each original number diagonally down to the right. Let’s use pictures to describe this:
We start with this:
And We Need To End Up With This:
Notice how we keep each row from above (bold text) and make three copies of this row.
Then, we repeat each number diagonally down to the right.
Shouldn’t We Normalize The Data?
Yes it would make sense to normalize our data into columns. This would make it easier to analyze the data with PivotTables and/or formulas. However, we haven’t been asked to normalize the data but rather repeat the rows and cascade the numbers.
VBA or Formulas?
If this is a repetitive task then automating it with Excel VBA is a great idea! What if you don’t have a programmer and you’ve got a pending deadline?
In that case we would naturally consider using either PowerQuery (Get & Transform) or formulas. Are there any other options?
Solution without VBA or Functions!
I solved this without VBA, PowerQuery or Functions. I combined several tricks that I’ve learned over the years (Thanks Bob Umlas!). I was able to repeat the rows and cascade the numbers in less than 5 minutes! Download my Excel file here or from my OneDrive.
Watch My YouTube Video
About Me
My name is Kevin Lehrbass. I live in Markham, Ontario, Canada. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. Currently, I’m a Data Analyst.
Hundreds of millions of people use Excel everyday. Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and my blog posts.
Away from Excel I enjoy playing with my dog Cali (she doesn’t like Excel), learning Spanish and playing Chess.