Pivot Tables on uneven flat lists . .

[Adam.Yakuvitz]

Hello,

I am trying to pivot table an uneven flat list that looks something
like below . .

Rep Q2 Rep Q1 Rep Sales
=========================
id1 300 id3 210 id3 3432
id2 240 id1 190 id2 2390
id4 235 id3 177 id1 1920
id3 144 id2 168 id4 1700
id4 130 id5 900
id6 545
id7 455

I need a pivot table that appropriately shows

Rep -> Sum(Q1, Q2)
Rep -> Sales
Rep -> Sales/Sum(Q1,Q2)

Whenever I try to pivot table the above list,
it lumps the Q2 & Q1 numbers by row, i.e
it ignores the Rep id key for. adds 300 (id1-q2)
+ 210 (id3-q1) = 510, when instead it should
add 300 (id1-q2) + 190 (id1-q1)= 490.

Thanks!
Adam

 

[Debra Dalgleish]

To create a pivot table from that sums the Rep results, you can
restructure the data.
Put all the Rep numbers in the same column.
In another column, enter the amounts
In a third column, enter the type, e.g. Q2

Then, create a pivot table from the restructured data table.

 

[Adam.Yakuvitz]

Hmm,
I was hoping I would'nt have to do any manual
data restructuring. I have data of 500+ rep id's.
So I don't think I would be able to manually
do this. I though the whole purpose of a pivot table
is bring cohesion to a multi-variant list such as this.
Maybe it's not . .

Can somebody suggest a script idea to do
"restructuring" as how Debra recommended here?

Thanks!
Adam

 

[steven1001]

You could try the following. Get the source data in Excel with columns
like Rep1, Value1, Rep2, Value2, Rep3,Value3. Save/close the file.

In another spreadsheet use Import External Data, Database query.
Connect to the first spreadsheet. In MSquery, add the first 2 columns
to the query. Click on the SQL button, it will look like:
SELECT `data$`.rep1, `data$`.value1
FROM `C:\DOCUMENTS AND SETTINGS\ADMINISTRATOR\MY
DOCUMENTS\data`.`data$` `data$`

<<<use copy/paste to turn add the following >>>>

union all
SELECT `data$`.rep2, `data$`.value2
FROM `C:\DOCUMENTS AND SETTINGS\ADMINISTRATOR\MY
DOCUMENTS\data`.`data$` `data$`
union all
SELECT `data$`.rep3, `data$`.value3
FROM `C:\DOCUMENTS AND SETTINGS\ADMINISTRATOR\MY
DOCUMENTS\data`.`data$` `data$`

This will then return 2 columns of info named Rep1, Value1 containing
the values from all columns in the source data. The values even added
to the same as in your example

You can then pivot table off the returned data set, you may need to
delete some blanks first.

regards..

 

[makkarm]

No need to restructure the data or go through a messy re-import
process.

You can just use the "multiple consolidated ranges" option while
pivoting your table. Just block select each of the rep-value columns
fields and add them one by one. Pivotting will appropriately lump the
rep and their corresponding values. You can also do your Sales/Sum
calculation as well.

Enjoy!
Masood