Convert table by date to array

[Toon]

Hello,
I'am having the following table.

Activity 01-01 01-02 01-03 01-04
01-05 etc
Item 1 123 222 567
222 456
Item 2 342 213 456
183 234
Item 3 87 234 345
213 234
etc

Iam searching for a macro which convert this format to a raw-data
format to use for pivot and further analysis.

Activity Date Number
Item 1 01-01 123
Item 1 01-02 222
Item 1 01-03 567
Item 1 01-04 222
Item 1 01-05 456
Item 2 01-01 342
Item 2 01-02 213
Item 2 01-03 456
etc....
etc...

Who can help me ?

Kind Regards,
Toon

 

[Pete_UK]

Assume that your table is in Sheet2, then you can put these formulae
in another sheet in the cells stated:

A2: =INDEX(Sheet2!A$2:A$4,INT((ROW(Sheet2!A1)-1)/5)+1)

B2: =INDEX(Sheet2!B$1:F$1,MOD(ROW(Sheet2!A1)-1,5)+1)

C2: =INDEX(Sheet2!B$2:F$4,INT((ROW(Sheet2!A1)-1)/5)+1,MOD(ROW
(Sheet2!A1)-1,5)+1)

These assume that you have five columns of data in your table (i.e
columns B to F) and that these occupy rows 2 to 4 with headers in row
1 and in column A. So, if you have more data than in your example (you
said "etc") then change all the 5s to the number of columns, and
change range references to suit.

Then you can copy the data down as far as required.

Hope this helps.

Pete

 

[Herbert Seidenberg]

Excel 2007 PivotTable
No formulas needed.
http://www.mediafire.com/file/m2wjtgw0rlo/11_09_09.xlsx

 

[Pete_UK]

And what's wrong with formulas, Herbert?

Pete

 

[Toon]

And what's wrong with formulas, Herbert?

Pete

Thanks for your responses

Tried both methods but not working yet.
With the formula method i get an error message #NAME
And the pivot option is not supported in Excel 2003 ?

 

[David Biddulph]

If you get a #NAME! error from the formula, it sounds as if you've typed it
wrongly.
Don't try to retype; copy the formula from the newsgroup and paste into the
formula bar of your spreadsheet.
If in doubt, copy back from your formula bar and paste here so that we can
see exactly what formula is giving you an error.
--
David Biddulph

And what's wrong with formulas, Herbert?

Pete

Thanks for your responses

Tried both methods but not working yet.
With the formula method i get an error message #NAME
And the pivot option is not supported in Excel 2003 ?

 

[Herbert Seidenberg]

Excel 2003
Here are both methods:
Pivot Table and (nice) Formulas
http://www.mediafire.com/file/mmj0hdimn1t/11_09_09.xls