reformating data for Excel pivot tables

[Anne Nasser]

I'd like to know if there is a straightforward way to create pivot
tables in excel for data which does not have one data element per line.
For example, I'd like to create pivot tables for very large data sets
for which the data is given for multiple years which run across
horizontally rather than under one column heading called "year".

For example:

fields structured as follows:
productname; 1991sales; 1992sales; 1993sales; 1994sales

instead of as (which is the standard way for pivot tables):
productname; year ; data

Is there a way to reformat the data to create pivot tables using the
already existent excel functionality? I am also wondering if it might
be possible to convert a dataset of multiple year format to an OLAP
cube. Or would it be necessary to write a VBA macro for this? Any
suggestions on how to approach this or go about this would be much
appreciated.

Anne Nasser

 

[Ken Wright]

Take a look at the following tip on John Walkenbach's site for creating a
database Table from a Summary table such as you describe:-

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

You can then use this as your source table

 

[Debra Dalgleish]

To restructure your data, you may be able to use the "unpivot' technique
described by John Walkenbach:
http://j-walk.com/ss/excel/usertips/tip068.htm

Before you start, change the "yearsales" headings to year only