Sort data in rows to column

[Venice]

I would need to re-arrange the format of the data. My current data is:
In row 1, every 3 columns has headers Opening, Spent, End balance (ie A1 is
Opening, B1 is Spent, C1 is End bal), the same Opening, Spent, End balance
repeats in columns D to F, G to I, so on). The data (of numbres) is at Row 2.

My intention is to have i column for Opening, 1 for Spent and 1 for End
balance. Hence, I can do pivot from the new format. How can I do that without
copying each 3 columns one by one to the new format. Any other excel
functions can help me to rearrange quicker. Thanks.

 

[Jacob Skaria]

In cell A3 apply this formula and copy across to B3,C3. once done copy the
formulas down as required...

=INDEX($2:$2,ROW(A1)*3+COLUMN(A1))

If this post helps click Yes