Transpose data by formula?

[CROY1985]

Hi all,

I need some help in writing a formula or macro that will help move data from one format to another.

This is how the data appears in its raw form:
https://picasaweb.google.com/croy1985/Excel#5650335384186221202

This is how i want it to look:
https://picasaweb.google.com/croy1985/Excel#5650335378957041906

Basically, All of the figures from B7 onwards need to be transposed to form one (very long colum of figures) and from this reference back to its client group, and KPI title. At the moment I am having to copy each line and paste special and tick the transpose option. All of the rest i can create using formula.

Hope you can help with this.

Thanks

 

[Weaver]

If your data is on sheet1, starting in A1, try this formula in A1 on a new sheet

=OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-1,1,1)

Then copy across and down as required.

HTH

 

[CROY1985]

Right, we're about half way there i think.

The formula you provided almost does everything, except, all of the raw data is now transposed from rows into columns. I want it all in one column (in a long list) and then in the column next to it the reference to Payment group, and another column would have the reference to the KPI.

Can this even be done with formula?