Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot
Is it possible to transpose (pivot) data in excel? I know that there
is a pivot option is excel but I could not get excel to work it the way I wanted. Suppose I have the following table (the second row has the column headers. Therefore, the first row is the excel column headers A through E. Then the next row has the column headers. A and B are blank or can have column headers such as 'id' and 'fruit'. The next three column headers are date values that need to be transposed: A B C D E blank blank 1/1/02 1/2/02 1/3/02 1 Orange 10 20 30 2 Apple 5 10 15 1 Grape 10 10 10 I was expecting something like following: 1 Orange 1/1/02 10 1 Orange 1/2/02 20 1 Orange 1/3/02 30 2 Apple 1/1/02 5 2 Apple 1/2/02 10 2 Apple 1/3/02 15 1 Grape 1/1/02 10 1 Grape 1/2/02 10 1 Grape 1/3/02 10 Is it possible and how? Also, I know there are other tools that are available to do this type of manipulation (transposition) but if excel can do this then I don't have to use tools from third party. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot
On Mar 7, 11:09 am, Debra Dalgleish wrote:
To reorganize the data, you can use the 'unpivot' technique described by John Walkenbach: http://j-walk.com/ss/excel/usertips/tip068.htm wrote: Is it possible to transpose (pivot) data in excel? I know that there is a pivot option is excel but I could not get excel to work it the way I wanted. Suppose I have the following table (the second row has the column headers. Therefore, the first row is the excel column headers A through E. Then the next row has the column headers. A and B are blank or can have column headers such as 'id' and 'fruit'. The next three column headers are date values that need to be transposed: A B C D E blank blank 1/1/02 1/2/02 1/3/02 1 Orange 10 20 30 2 Apple 5 10 15 1 Grape 10 10 10 I was expecting something like following: 1 Orange 1/1/02 10 1 Orange 1/2/02 20 1 Orange 1/3/02 30 2 Apple 1/1/02 5 2 Apple 1/2/02 10 2 Apple 1/3/02 15 1 Grape 1/1/02 10 1 Grape 1/2/02 10 1 Grape 1/3/02 10 Is it possible and how? Also, I know there are other tools that are available to do this type of manipulation (transposition) but if excel can do this then I don't have to use tools from third party. Thanks. -- Debra Dalgleish Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text - - Show quoted text - Thanks Debra. When I tried it I could get only ONE column based pivot. In Step 2b of 3 when I mention the Range, I can give only one column. For example A1:E4 or B1:E4 Therefore I get either A1 or B1 in the final result (with the pivoted result) but not both. However, this is still okay because I can manually copy and paste data and recreate the new table manually. My actual data has more columns and it looks something like: Product Country Manufact...MTH/1/02 MTH/2/02...SU/1/02 SU/2/02...EUR/1/02 EUR/2/02 etc (data for 5 years) If I can get a complex tool where I can define all the fields and get the final output in one shot (instead of doing manually for all columns) that would be great. There are tools available for example pivot.exe that takes xml file as input and does the job exactly outlined using a defined format. But xml is not my expertise. Thanks again. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot
Sorry, I read that first column as your row numbers, instead of values
in column A. Before you 'unpivot' the data, concatenate the data in the first three columns (product/country/manuf). For example: 1. Insert a blank column D, and enter a heading in row 1, e.g. "Info" 2. In row 2, enter the following formula, and copy down to all rows: =A2& "^"&B2&"^"&C2 3. Copy the formula to the end of the data 4. On a blank worksheet, select cell A1 Then, follow John Walkenbach's instructions to unpivot the data, using column D and the columns to the right. To split the concatenated column, 1. Move the Info column to the right end of the data 2. Select the Info column 3. Choose DataText to Columns 4. Choose Delimited, click Next 5. In the 'Other' box, type: ^ 6. Click Finish 7. Return the info columns to the far left of the data. wrote: On Mar 7, 11:09 am, Debra Dalgleish wrote: To reorganize the data, you can use the 'unpivot' technique described by John Walkenbach: http://j-walk.com/ss/excel/usertips/tip068.htm wrote: Is it possible to transpose (pivot) data in excel? I know that there is a pivot option is excel but I could not get excel to work it the way I wanted. Suppose I have the following table (the second row has the column headers. Therefore, the first row is the excel column headers A through E. Then the next row has the column headers. A and B are blank or can have column headers such as 'id' and 'fruit'. The next three column headers are date values that need to be transposed: A B C D E blank blank 1/1/02 1/2/02 1/3/02 1 Orange 10 20 30 2 Apple 5 10 15 1 Grape 10 10 10 I was expecting something like following: 1 Orange 1/1/02 10 1 Orange 1/2/02 20 1 Orange 1/3/02 30 2 Apple 1/1/02 5 2 Apple 1/2/02 10 2 Apple 1/3/02 15 1 Grape 1/1/02 10 1 Grape 1/2/02 10 1 Grape 1/3/02 10 Is it possible and how? Also, I know there are other tools that are available to do this type of manipulation (transposition) but if excel can do this then I don't have to use tools from third party. Thanks. -- Debra Dalgleish Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text - - Show quoted text - Thanks Debra. When I tried it I could get only ONE column based pivot. In Step 2b of 3 when I mention the Range, I can give only one column. For example A1:E4 or B1:E4 Therefore I get either A1 or B1 in the final result (with the pivoted result) but not both. However, this is still okay because I can manually copy and paste data and recreate the new table manually. My actual data has more columns and it looks something like: Product Country Manufact...MTH/1/02 MTH/2/02...SU/1/02 SU/2/02...EUR/1/02 EUR/2/02 etc (data for 5 years) If I can get a complex tool where I can define all the fields and get the final output in one shot (instead of doing manually for all columns) that would be great. There are tools available for example pivot.exe that takes xml file as input and does the job exactly outlined using a defined format. But xml is not my expertise. Thanks again. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
build a pivot table from multiple other pivot tables. | Excel Discussion (Misc queries) | |||
Pivot Tables -changing datasource for exsting Pivot Table | Setting up and Configuration of Excel | |||
deactivate the get pivot formula when I link a cell to a pivot | New Users to Excel | |||
Can links between Excel 2003 Pivot Charts and their pivot table b. | Charts and Charting in Excel | |||
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. | Excel Discussion (Misc queries) |