Combining data, should i use pivottable or something else?

[Funbags]

Hello,

Below is a quick example of what I am trying to do. Baiscly I have a UPC code which is the comon element that can be compaired. I want to merge anything that has the same upc code to total it up. I thought to use a pivottable, but for some reason it doesnt total them. Any Help is appreciated!

Example data:

UPC	BackOrder	Inventory	On Order
679420014586	0	19	0
679420014586	2	47	5
679420014586	0	1	0
679420014845	0	1	0
679420014845	2	0	0


I want the output to do this (combine them):

UPC	BackOrder	Inventory	On Order
679420014586	2	67	5
679420014845	2	1	0

 

[Funbags]

Re: Combining data, should i use pivottable or something els

The problem I am having with using a pivottable is it doesnt combine them and it doesnt list the UPC next to each item, below is the output from the pivottable:

Book3
	A	B	C	D
1	UPC	BackOrder	Inventory	OnOrder
2	679420014586	0	1	0
3			19	0
4		2	47	5
5	679420014845	0	1	0
6		2	0	0
Sheet1

I tried many differnent ways, I know there has to be a way Thanks guys!

 

[Norie]

As far as I know a pivot table wouldn't organize your data like you describe in your first post.

 

[KenWright]

Pivot table output on your data with no tweaks - is this what you wanted:-

Book1
	A	B	C	D
3		Data
4	UPC	SumofBackOrder	SumofInventory	SumofOnOrder
5	679420014586	2	67	5
6	679420014845	2	1	0
7	GrandTotal	4	68	5
Sheet4

 

[Norie]

Ken

What version of Excel are you using?

I tried in 2000 with the OP's data and couldn't get that result.

I'm sure it's possible but I'm more used to using pivot tables in 97.

EDIT Finally got it by dragging the fields about.

 

[KenWright]

For the OPs benefit now:-

Data / Pivot Table & Chart report - Hit Next / Next / Finish

Drag UPC field to the left into the ROW fields
Drag Backorder field into the DATA field
Drag Inventory field into the DATA field
Drag On Order field into the DATA field

backorder/Inv/OnOrd will now all be on top of each other, and at the top of the table you will see they all sit under a grey button marked Data. Grab that button and drag it to the Top right of the table and the data will now be horizontal

 

[Norie]

Grab that button and drag it to the Top right of the table and the data will now be horizontal

That's exactly what I would do in 97.

In 2000, for some reason when you do that it 'removes' the data items.

 

[KenWright]

Jut tried in 2K as well, and it works just the same for me. Table prior to that last drag looks like this

Book1
	A	B	C	D
3	UPC	Data	Total
4	679420014586	SumofBackOrder	2
5		SumofInventory	67
6		SumofOnOrder	5
7	679420014845	SumofBackOrder	2
8		SumofInventory	1
9		SumofOnOrder	0
10	TotalSumofBackOrder		4
11	TotalSumofInventory		68
12	TotalSumofOnOrder		5
Sheet4

and then I drag the Data button to what is currently C3 and contains the word 'Total'

 

[Norie]

I finally got it by dragging data on to Total - I think my problem was I was dragging it off the table.

By doing that Excel things you want to delete it I suppose.

I'm sure I never happened with 97.

 

[Funbags]

Pivot table output on your data with no tweaks - is this what you wanted:-

Thats exactly what I want to do! how did you do it?