Excel 2016 - Data Duplicates Help

[mschario]

Hello, I am trying to sort data so that I can place it into a pivot table.

An example of the data is below.

The main issue is that I have duplicates on the ON HAND and Demand Average columns because there are PO Numbers that are unique tied to them. I receive this excel on a regular basis so I am hoping to potentially copy the data into another workbook that has additional columns setup to remove duplicates easily and then view in a pivot table.

So for Item 37010146, I would want to see what the weekly movement is (215), in location 32 and show on hand (202), but realizing that I have ON ORDER at 720 because it has two PO Numbers.

I am using excel 2016. If anyone has any ideas how to adjust the data, I am all ears!

Thank you to anyone who can help!

Location #	Item #	WEEKLY MOVEMENT	ON HAND	DEMAND AVG	PO NUMBER	ON ORDER
32​	37010146​	215​	202​	259.4​	909006​	360​
32​	37010146​	215​	202​	259.4​	910405​	360​
29​	37010146​	207.5​	40​	273.3​	533336​	180​
29​	37010146​	207.5​	40​	273.3​	493198​	180​
29​	37010146​	207.5​	40​	273.3​	495277​	180​
29​	37010146​	207.5​	40​	273.3​	497905​	180​
17​	37010146​	190​	283​	130.5​	655696​	180​
17​	37010146​	190​	283​	130.5​	653575​	180​
20​	37010146​	172.7​	449​	182.2​	246490​	180​
27​	37010146​	171.6​	547​	191​	456989​	360​
27​	37010146​	171.6​	547​	191​	448438​	180​
25​	37010146​	119​	852​	195.5​
5​	37010146​	111.1​	180​	125.7​	908392​	180​
5​	37010146​	111.1​	180​	125.7​	911048​	180​
5​	37010146​	111.1​	180​	125.7​	912558​	180​
30​	37010146​	86​	204​	72.7​	818787​	180​
34​	37010146​	80​	241​	96​	791234​	180​
34​	37010146​	80​	241​	96​	793214​	180​
33​	37010146​	58.6​	161​	85.4​	781248​	180​
27​	37010146​	55​	82​	56.7​	446896​	180​
19​	37010146​	46.8​	312​	86​
27​	37010146​	23.8​	52​	24.5​	456994​	40​
27​	37010146​	23.8​	52​	24.5​	452749​	40​

 

[etaf]

how about using the weekly movement & on hand as a row in the pivot table - means its not manipulated
nothing to manipulate then
but maynot be a solution for you - if the real data is different to the sample

Book39
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Location #	Item #	WEEKLY MOVEMENT	ON HAND	DEMAND AVG	PO NUMBER	ON ORDER
2	32	37010146	215.00	202.00	259.4	909006	360.00			Location #	WEEKLY MOVEMENT	ON HAND	Sum of ON ORDER
3	32	37010146	215.00	202.00	259.4	910405	360.00			32	215	202	720
4	29	37010146	207.50	40.00	273.3	533336	180.00			29	207.5	40	720
5	29	37010146	207.50	40.00	273.3	493198	180.00			17	190	283	360
6	29	37010146	207.50	40.00	273.3	495277	180.00			20	172.7	449	180
7	29	37010146	207.50	40.00	273.3	497905	180.00			27	171.6	547	540
8	17	37010146	190.00	283.00	130.5	655696	180.00				55	82	180
9	17	37010146	190.00	283.00	130.5	653575	180.00				23.8	52	80
10	20	37010146	172.70	449.00	182.2	246490	180.00			25	119	852
11	27	37010146	171.60	547.00	191	456989	360.00			5	111.1	180	540
12	27	37010146	171.60	547.00	191	448438	180.00			30	86	204	180
13	25	37010146	119.00	852.00	195.5					34	80	241	360
14	5	37010146	111.10	180.00	125.7	908392	180.00			33	58.6	161	180
15	5	37010146	111.10	180.00	125.7	911048	180.00			19	46.8	312
16	5	37010146	111.10	180.00	125.7	912558	180.00
17	30	37010146	86.00	204.00	72.7	818787	180.00
18	34	37010146	80.00	241.00	96	791234	180.00
19	34	37010146	80.00	241.00	96	793214	180.00
20	33	37010146	58.60	161.00	85.4	781248	180.00
21	27	37010146	55.00	82.00	56.7	446896	180.00
22	19	37010146	46.80	312.00	86
23	27	37010146	23.80	52.00	24.5	456994	40.00
24	27	37010146	23.80	52.00	24.5	452749	40.00
Sheet1

this will only be on dropbox for a few days

pivottable ignoring duplicates - etaf.xlsx

Shared with Dropbox

www.dropbox.com