C
Craig
Hi,
I have a need to transpose time series data. I'll need to do this for a
number of spreadsheeets, so my thinking is that a macro to run on each sheet
would be the most efficient (it's actually 4 separate workbooks, each
containing around 5 sheets).
The data is currently in this format, more or less (there is a header row
with just dates, and a second header row with names - Balance, Contribution,
Withdrawal etc. The one thing I should add is that column A has just the name
header - ID - but no date. The ID column appears only once in column A,
whereas for Balance, Contribution and Withdrawal, there is one column for
each date):
12/31/05 12/31/05 12/31/05 1/31/06 1/31/06 1/31/06
ID Balance Contribution Withdrawal Balance Contribution Withdrawal
1 $100 $0 $75 $25 $0 $0
2 $500 $25 $0 $525 $0 $0
I need to get it to this format:
ID Date Balance Contribution Withdrawal
1 12/31/05 $100 $0 $75
2 12/31/05 $500 $25 $0
1 1/31/06 $25 $0 $0
2 1/31/06 $525 $0 $0
I have a need to transpose time series data. I'll need to do this for a
number of spreadsheeets, so my thinking is that a macro to run on each sheet
would be the most efficient (it's actually 4 separate workbooks, each
containing around 5 sheets).
The data is currently in this format, more or less (there is a header row
with just dates, and a second header row with names - Balance, Contribution,
Withdrawal etc. The one thing I should add is that column A has just the name
header - ID - but no date. The ID column appears only once in column A,
whereas for Balance, Contribution and Withdrawal, there is one column for
each date):
12/31/05 12/31/05 12/31/05 1/31/06 1/31/06 1/31/06
ID Balance Contribution Withdrawal Balance Contribution Withdrawal
1 $100 $0 $75 $25 $0 $0
2 $500 $25 $0 $525 $0 $0
I need to get it to this format:
ID Date Balance Contribution Withdrawal
1 12/31/05 $100 $0 $75
2 12/31/05 $500 $25 $0
1 1/31/06 $25 $0 $0
2 1/31/06 $525 $0 $0