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:

Product A	Product B	Quantity - product A	Quantity - product B
ID14	ID2	10	4
ID14		12
ID2		10
ID16	ID14	20	7
ID17	ID16	10	11

<tbody>
</tbody>

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
1	Product	ID	Quantity
2	Product A	ID14	10
3	Product A	ID14	12
4	Product A	ID2	10
5	Product A	ID16	20
6	Product A	ID17	10
7	Product B	ID2	4
8	Product B	ID14	7
9	Product B	ID16	11

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:67px;"><col style="width:34px;"><col style="width:61px;"></colgroup><tbody>
</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.