Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert table by date to array
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date range table array formula | Excel Worksheet Functions | |||
Convert date field to month in Pivot table | Excel Discussion (Misc queries) | |||
Convert column to array? | Excel Discussion (Misc queries) | |||
How do i convert row array into one column using pivot table | Excel Worksheet Functions | |||
how to convert an array | Excel Discussion (Misc queries) |