create pivot table for non-normalized data

[cdelarte]

Hi Folks,

I need to create a pivot table based on un-normalized data arranged
similar to the following with the objective being to show the total
amount of each item that has been sold. Can I get a total count of
each item by direct manipulation in a pivot table, or do I need to
calculate this first in the source data?

date, TxnID, item1, item2, item3
jan1, 1, apple, banana, apple
jan2, 2, apple, apple, orange
jan3, 3, apple, banana, orange
jan3, 4, orange,orange,orange

Thanks

 

[michael vardinghus]

U can get it direct

I dont where ur source data are located by try entering
ur example in a spreadsheet and put pivottable on top.

To play witht these example data might give u idea
of the concept.

However. ur example data is a little bit weird
because u repeat values in differnt dimensions.

For instance u have apple in both item1 and item3 -
seem weird.

If u want to create distinct counts and not sum up values it takes more.

 

[Herbert Seidenberg]

Excel 2007
Use Reverse Pivot Table:
http://www.savefile.com/files/1835074

 

[Max]

Here's a simple way using formulas
to transform it into the desired columnar format for downstream pivoting

Illustrated in this sample:
http://freefilehosting.net/download/40lhl
Transform cross tab data into columnar data for pivoting.xls

Assume source data as posted is in Sheet1's cols A to E, data from row2 down
In another sheet,
You could have these col labels in A11, eg:
date, TxnID, Item, Content
Then just place
In A2: =OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/3),)
In B2: =OFFSET(Sheet1!$B$2,INT((ROWS($1:1)-1)/3),)
In C2: =INDEX(Sheet1!$B$1:$D$1,MOD(ROWS($1:1)-1,3)+1)
In D2: =OFFSET(Sheet1!$B$2,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3))
Select A22, copy down as far as required to exhaust the source data.
Format col A as dates to taste. Adapt the "3" in the expressions to suit the
no. of source cols for "items"

This is the desired results that you'd get (which you could easily pivot
on):
date TxnID Item Content
Jan-01 1 item1 apple
Jan-01 1 item2 banana
Jan-01 1 item3 apple
Jan-02 2 item1 apple
Jan-02 2 item2 apple
Jan-02 2 item3 orange
etc
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000, Files:362, Subscribers:61
xdemechanik

 

[Max]

Apologies, posted wrongly the formulas in C2 and D2 earlier
It should have read as:
In C2: =INDEX(Sheet1!$C$1:$E$1,MOD(ROWS($1:1)-1,3)+1)
In D2: =OFFSET(Sheet1!$C$2,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3))
(The sample's fine though)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---

 

[Max]

Revised sample (documented formulae corrections):
http://freefilehosting.net/download/40m1l
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---