pivot

[othellomy]

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]

To reorganize the data, you can use the 'unpivot' technique described by
John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

 

[othellomy]

To reorganize the data, you can use the 'unpivot' technique described by
John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm










--
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]

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 Data>Text 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.