Possible to use pivot table on non-mumeric fact data withoutcalculating?

[StefanM]

Hi,

does anybody know if it is possible to use a pivot table and not do
any calculation on the fact data? I have a flat table in my Orace DB
and just want to use Excel's pivot table to transform the data into
columns (X axis) and rows (Y axis) displaying the non-numeric fact
data. The combination of X- and Y-dimension data is unique.

My fact data is a 1-letter alphanumeric code encoding various options
for the X- and Y-dimension data with the letters O, M, A and G. To
work arround I decode the alphanumeric code into 1-digit numbers
0,1,2,3 and use "maximum" as formula for the pivot table. As the
combination of X/Y is unique the pivot table displays the need values
in the matrix. But I would prefer the alphanumeric values as O, M, A
and G have more meaning to the end user than 0, 1, 2, 3.

TIA,
Stefan

 

[Dave Peterson]

Maybe you should look at John Walkenbach's "reverse pivot table" technique:
http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

 

[StefanM]

Dave,

thanks for the link but I just don't understand how this would help
me. If you take a look at the sample data in the example, my data in
the DB looks much like the data on the right side (this is what I
called a "flat" table in my post), except that the "Sales" column is
my "Option" column and contains one of the lettes O,M,A,G. The result
should look like the table on left side displaying the corresponding
"option" for each combination of "Product" (X-axis) and "Month" (Y-
axis). So the data from the "flat" table was transformed/pivoted to a
two dimensional table with products as X-axis and months as Y-axis.

Stefan

 

[Dave Peterson]

You could build your header row of products and header column of rows, then use
a formula like this in each cell:

=index(othersheet!$c$1:$c$100,
match(1,($a1=othersheet!$a$1:$a$100)
*(b$1=othersheet!$b$1:$b$100),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.