Convert table by date to array

  • Thread starter Thread starter Toon
  • Start date Start date
T

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
 
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
 
And what's wrong with formulas, Herbert?

Pete
Click to expand...

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 ?
 
Back
Top