PIVOT - combine two columns in one field

[AmNimru]

Hello everyone,

Is there a way I could combine the data from two columns in one field in PIVOT table?
The data looks something like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Product A[/TD]
[TD]Product B[/TD]
[TD]Quantity - product A[/TD]
[TD]Quantity - product B[/TD]
[/TR]
[TR]
[TD]ID14[/TD]
[TD]ID2[/TD]
[TD]10[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]ID14[/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID2[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID16[/TD]
[TD]ID14[/TD]
[TD]20[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]ID17[/TD]
[TD]ID16[/TD]
[TD]10[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]


So, I would like to have in one PIVOT field combined products from both columns A and B (with removed duplicates) and the sum of quantities from both column C and D, depending in which column the product is placed.
It would be the same as using SUMIF from field C if product is in column A + SUMIF from field D if product is in column B.

Anyone has any idea how to do this?

 

[Sinon]

You would have to rearrange your table so you have 1 column with the product name, 1 column with the ID and one column with the quantity. Something like:

*	A	B	C
Product	ID	Quantity
Product A	ID14
Product A	ID14
Product A	ID2
Product A	ID16
Product A	ID17
Product B	ID2
Product B	ID14
Product B	ID16

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:67px;"><col style="width:34px;"><col style="width:61px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]11[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

You can then insert a Pivot and put the Product and ID in the ROWS field and the Quantity in the Values field.

 

[AmNimru]

Thanks for you reply!
I was afraid I might have to do that (and from 300.000 rows of data get 450.000), but I was really hoping I could avoid it.