Pivoting Data for value :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Pivoting Data for value
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

scharny
Welcome to the Board


Joined: 19 Sep 2003
Posts: 2

Flag: Usa

Status: Offline

 Reply with quote  

Pivoting Data for value

I have a worksheet that looks something like this:

Product Fruit Type Size
Product1 Apples Red Delicious L
Product2 Apples Red Delicious M
Product3 Apples Golden S

I am trying to pivot the data to look like this:

Product Classification Detail
Product1 Fruit Apple
Product1 Type Red Delicious
Product1 Size L
Product2 Fruit Apple
Product2 Type Red Delicious
Product2 Size M
Product3 Fruit Apple
Product3 Type Golden
Product3 Size S


Other than a pivot table is there any other way to accomplish this?

Post Fri Sep 19, 2003 4:13 pm 
 View user's profile Send private message

Mark W.
MrExcel MVP


Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag: Usa

Status: Offline

 Reply with quote  

Re: Pivoting Data for value

Yes, using this query and the Excel ODBC driver...

SELECT List.Product, 'Fruit' , List.Fruit
FROM `Book3`.List List
UNION
SELECT List.Product, 'Type' , List.Type
FROM `Book3`.List List
UNION
SELECT List.Product, 'Size' , List.Size
FROM `Book3`.List List
ORDER BY List.Product

Microsoft Excel - Book3.xls___Running: xl2002 XP : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
E
F
G
H
I
J
K
1
ProductFruitTypeSize  ProductClassificationDetail  
2
Product1ApplesRed DeliciousL  Product1FruitApples  
3
Product2ApplesRed DeliciousM  Product1SizeL  
4
Product3ApplesGoldenS  Product1TypeRed Delicious  
5
      Product2FruitApples  
6
      Product2SizeM  
7
      Product2TypeRed Delicious  
8
      Product3FruitApples  
9
      Product3SizeS  
10
      Product3TypeGolden  
11
           
12
           
13
           
Sheet1 

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Post Fri Sep 19, 2003 4:29 pm 
 View user's profile Send private message

scharny
Welcome to the Board


Joined: 19 Sep 2003
Posts: 2

Flag: Usa

Status: Offline

 Reply with quote  

Re: Pivoting Data for value

Perfect, thank you very much.

Post Fri Sep 19, 2003 4:53 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.