reformating data for Excel pivot tables

[Anne Nasser]

Many thanks for referring me to John Walkenback`s site for the tip on
using the "unpivot" technique to reformat the data. It`s an excellent
tip.

J. Walkenback describes how data can be reformatted from a two-variable
summary table. Is there a way that this could be done with a
three-variable summary table or perhaps even larger summary table.

For example, if the original summary table is structured as follows:

productname; region; 1991sales; 1992sales; 1993sales
automobiles; detroit; 400; 503; 600
automobiles; canada; 561; 389; 300
automobiles; alabama; 388; 493; 789
toasters; canada; 987; 234; 343
radios; detroit; 343; 434; 789
(the only repeating field per line is sales per year)

and I would like to reformat the data to the standard structure for
pivot tables:
productname; region; year; data

Is there a way that Walkenback`s technique could be applied to do this
for a three-variable table? Or is there another way you recommend? Any
suggestions on how to approach this or go about this would be much
appreciated.

Anne Nasser

 

[Debra Dalgleish]

Insert a column between region and 1991 sales
Use a formula to combine the data in the product and region fields:
=A2 & "|" & B2
Follow the instructions to the "unpivot" the data, using
columns C:F
In the resulting table, insert a column between columns A and B
Select the data in column A, and choose Data>Text to Columns
Choose Delimited, click Next
Check Other, and type | as the delimiter, click Finish

 

[Anne Nasser]

Very many thanks for the formula data combination tip on reformatting a
three-variable summary table for a pivot table! It worked very well.

I am wondering if you might also know how I could create a pivot table
in excel using data from several sources. Using the Pivot Wizard, the
create table "consolidating different sources" seems to be on the right
track.
However, I would like to, for example, create a table consolidating the
following four sources:

1) excel worksheet
2) excel worksheet
3) external database through a query link (with automatic update)
4) another external database through a query link (with automatic
update)

As you can see, it is a "consolidation" of sources, but it also has
elements of the "external database" option. Is there a way that this
could be done using the existent excel functionality or would another
approach be
needed? Your suggestions would be much appreciated.

Anne Nasser