Transposing Data

Hi!

I have data from my company's accounting system in the format where each field is a separate column: Account, Description, Cost Center, and then twelve columns where each represents one month of data.  I need to re-format so that I have only five columns or fields: Account, Description, Cost Center, Period/Month, Value.  I know this is probably very basic, and I know how I can do it manually.  I would really appreciate any advice for a more elegant solution. 

I tried a search as I am sure this has probably been answered before but not coming up with anything.

Most grateful for your time!

Hi

I would create a dynamic range to represent your existing data.

Assuming it is on sheet1, create a Name Data with a formula of
 =Sheet1!$A$1:INDEX(Sheet1!$O:$O,COUNTA(Sheet1!$A:$A))

On sheet 2 st up headings in A1 to E1 of Account, Description, Cost Centre, Month and Value
In cell A2 enter the formula
 =INDEX(data,2+INT(ROW(A1)/13),COLUMN())

and copy across through B2:C2

In cell D2 type Jan

In cell E2 enter
=INDEX(data,2+INT(ROW(A1)/13),3+MONTH(D2&1))

Now, copy A2:E2 down the page until you eventually see #REF! appearing. This will happen when all of your data has been transposed.

Hi

I should have added, that having completed this task, copy all of the data on sheet2 then Paste Special>Values to fix the values in place of the formulae.

Then, if your first month in your source sheet is say Apr, rather than Jan, change the value in D2 to Apr, and fill down the column.