transpose data ????

[whiZZfiZZ]

Gidday all.

Hoping someone can help me out with a real challenge on this one.

I have a spreadsheet that contains account numbers (for our accountin
system) in one column and the months in 12 other columns.


heres what I would like to be able to do. I need to create anothe
worksheet with the following lay out.

I need to have 3 columns, one with the account number, the amount, an
the month, like this

Account Amount Month

1.11 100.00 31/07/04
1.11 200.00 31/08/04
etc..
1.12 100.00 31/07/04
1.12 200.00 31/08/04
etc..

each account number is listed down the page for each month, and th
corresponding amount is picked up for the account and the relevan
month. the amounts in the amount column need to be cumulative.

In the past, it has been copied and pasted manually (the worksheet i
about 3000 rows long so it usually takes about a day or two !!!!)

Can anyone please help me with a way to achieve this ? your help woul
be greatly appreciated.
Thank you

 

[whiZZfiZZ]

im getting better with formula's.. but i know nothing about how t
create a marco to do this

 

[Debra Dalgleish]

To reorganize the data, you can use the "unpivot' technique described by
John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

 

[whiZZfiZZ]

ahhhhh "excel"lent the unpivot table worked fairly well.. it didn
accumulate the figures thou.. i'll do a bit more reading about pi
tables and see if i can figure it out..

thank you so much for pointing me in the right direction

cheerZ

 

[duane]

This macro worked for me....John....note a couple of names ar
required...

Option Explicit
Sub Macro1()
'
' Macro1 Macro
'
Dim i As Integer
Dim j As Integer
Dim month(12) As Variant
Dim monthamount(12, 100) As Double
Dim account(100) As Variant
'
' Acountcell is range name of cell at head of account column
' with months strung out to the right
' Rawdata is sheet with raw data on it
' Transposed is sheet to receive Transposed Data
'
Sheets("rawdata").Select
For i = 1 To Range("accountcell").End(xlDown).Row - _
Range("accountcell").Row
account(i) = Cells(i + Range("accountcell").Row, 1).Value
For j = 1 To Range("accountcell").End(xlToRight).Column - _
Range("accountcell").Column
month(j) = Cells(Range("accountcell").Row, j + _
Range("accountcell").Column).Value
monthamount(j, i) = Cells(i + Range("accountcell").Row, j + _
Range("accountcell").Column).Value
Next j
Next i
'
Sheets("transposed").Select
'
Cells(2, 1).Value = "Account"
Cells(2, 2).Value = "Amount"
Cells(2, 3).Value = "Month"
'
For i = 1 To Range("accountcell").End(xlDown).Row - _
Range("accountcell").Row
For j = 1 To Range("accountcell").End(xlToRight).Column - _
Range("accountcell").Column
'
Cells((i - 1) * (Range("accountcell").End(xlToRight).Column - _
Range("accountcell").Column) + j + 2, 1).Value = account(i)
Cells((i - 1) * (Range("accountcell").End(xlToRight).Column - _
Range("accountcell").Column) + j + 2, 2).Value = monthamount(j, i)
Cells((i - 1) * (Range("accountcell").End(xlToRight).Column - _
Range("accountcell").Column) + j + 2, 3).Value = month(j)
Next j
Next i
Cells(1, 1).Select
End Su

 

[whiZZfiZZ]

that is awesome!!!!! how on earth did you come up with that so quickl
??

after i have adjusted range names, and run the macro, im getting a
error, the error dialog box has a red cross on it and the number 400 i
it

 

[duane]

I assume you are set up with the sheet names I described.

on "rawdata" sheet


Account Month1 Month2 etc in top row
12726
172829
etc

(no blank rows)

the cell where "Account" is should be named "accountcell" (insert rang
name....)

macro is limited to 100 accounts - to change this change 100 to wha
you need in macr

 

[whiZZfiZZ]

thanks heaps mate!!! i got it working ya just saved me about 2 day
excel work!!