Convert table by date to array
 

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

Convert table by date to array
 

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


On Nov 9, 8:24*pm, Toon wrote:
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

Convert table by date to array
 

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

Convert table by date to array
 

And what's wrong with formulas, Herbert?

Pete

On Nov 10, 1:46*am, Herbert Seidenberg
wrote:
Excel 2007 PivotTable
No formulas needed.http://www.mediafire.com/file/m2wjtgw0rlo/11_09_09.xlsx

Convert table by date to array
 

On 10 nov, 02:51, Pete_UK wrote:
And what's wrong with formulas, Herbert?

Pete

On Nov 10, 1:46*am, Herbert Seidenberg
wrote:

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

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 ?

Convert table by date to array
 

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

"Toon" wrote in message
...
On 10 nov, 02:51, Pete_UK wrote:
And what's wrong with formulas, Herbert?

Pete

On Nov 10, 1:46 am, Herbert Seidenberg
wrote:

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

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 ?

Convert table by date to array
 

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