Stack every nth column under neath each other

Question

This may be a trivial problem however I am unable to figure it out. I have a dataset which has duplicate columns and I need to figure out a way to stack every nth column underneath each other. So if we consider this :

We can see we have duplicate columns, I need my data to stack underneath each other like this :

My duplicate columns are a constant, so I will always have 5 duplicate columns. Any help on this would be greatly appreciated!

Answer 1

You need a cyclical OFFSET formula here.

SETUP

So we have a header row, and data in, let's say, A2:ZZZZ38. We'll just put this on Sheet1. We'll set up our output on Sheet2.

PRECIS

Conceptually, we can easily understand that we want Sheet2!A2 (hereafter we'll just call this cell A2 for simplicity) to reproduce Sheet1!A2. In A3 we want the next cell down, and so on - until we've looked at all the records in Sheet1!A, and then we want to go back to the first row, but over 5 columns.

This is clearly a case for OFFSET.

FORMULA

=OFFSET(Sheet1!A$2,MOD(ROW()-2,COUNTA(Sheet1!A:A)-1),ROUNDDOWN((ROW()-2)/(COUNTA(Sheet1!A:A)-1),0)*5)

DEMO

My data is quite a bit smaller than yours; just big enough to prove concept.

Here's Sheet1:

And the output Sheet2:

EXPLANATION

OFFSET has three parts. Where to start, how many rows to go down, and how many columns to go over.

A - where to start

Sheet1!A$2

Pretty straightforward.

B - rows offset

MOD(ROW()-2,COUNTA(Sheet1!A:A)-1)

Find out what row we're on and subtract 2 (we're starting in A2, and we want A2 to be the zero offset). Divide that by how many records we're counting off (the -1 here accounts for the header row showing up), and take the remainder. This simply produces a function that counts from 0 to [1-n] where n is the number of rows, and then starts again at 0.

In other words, for a table with 8 records, this returns:

0, 1, 2, 3, 4, 5, 6, 7, 0, 1, 2, 3, 4, 5, 6, 7, 0 ...

C - columns offset

ROUNDDOWN((ROW()-2)/(COUNTA(Sheet1!A:A)-1),0)*5

Take ROW()-2 again, and divide it by the record count again - but this time instead of taking the MOD (remainder) of that relationship, we just want to round it down to the nearest integer. This gives us a sequence consisting of a number of 0's equal to the record count, followed by a number of 1's, 2's, etc. Then multiply that by 5 (see note below). This provides our column offset - every time the cyclical row count resets, we jump over another 5 columns.

In other words, for a table with 8 records, this returns:

0, 0, 0, 0, 0, 0, 0, 0, 5, 5, 5, 5, 5, 5, 5, 5, 10 ...

FORMULA, AGAIN:

=OFFSET(Sheet1!A$2,MOD(ROW()-2,COUNTA(Sheet1!A:A)-1),ROUNDDOWN((ROW()-2)/(COUNTA(Sheet1!A:A)-1),0)*5)

This goes into Sheet2!A2 and copies across for A:E and down without restriction.

NB

1. The 5 is hard-coded, because you stated as a property of your data that the columns repeat by 5. This could be expanded into a dynamic function, if needed. That can be a question for another day.
2. Despite the massive file size, this formula shouldn't take any time to process. It does include an entire column reference, but inside a COUNTA I don't think that will slow you down. If it does, you can easily change each instance of COUNTA(Sheet1!A:A) to COUNTA(Sheet1!A2:A38) since technically we already know the size of the data, or even simply hard code that value to 37, which will work as long as you have exactly 37 rows.
3. Half the time it took me to get this right was being careful with order of arithmetic operations and splitting out the parts of the formula into different columns to troubleshoot the problem I was having - which turned out to be a missing pair of parentheses. At its core, this is actually very simple; it's just a matter of knowing that OFFSET exists and then working out the logic/arithmetic to understand how to produce the sequence of pairs you need to offset by.