How to sum duplicate fields within a pivot table in excel 2010

[Tiffgringa]

I have created a pivot table that shows me a list of zip codes, the day of the week the zip codes are serviced, and how many different times it is serviced. The problem is I have duplicates zip codes, and I need to sum and then delete the duplicates. I'm very noviced excel user, so need step by step how to sum fields within a pivot.
My example:

Count of DAY
ZIP	DAY	Total
54016	Mon	4
	Tue	1
	Wed	2
54022	Fri	3
55003	Thu	1
55005		1
55014	Wed	3
	Thu	1
55016	Mon	1
	Wed	1
55024	Thu	1
55025	Wed	2
55033	Mon	1
	Wed	1
	Thu	1

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Oeldere]

I have duplicates zip codes, and I need to sum and then delete the duplicates

this text is not clear to me, i don't see duplicated zip codes in your example.

 

[Tiffgringa]

I only posted a snapshot....here's the full pivot. It's very large.

Count of DAY
ZIP	DAY	Total
54016	Mon	4
	Tue	1
	Wed	2
54022	Fri	3
55003	Thu	1
55005		1
55014	Wed	3
	Thu	1
55016	Mon	1
	Wed	1
55024	Thu	1
55025	Wed	2
55033	Mon	1
	Wed	1
	Thu	1
55038	Mon	1
	Thu	1
55044	Wed	4
55068	Wed	4
55075	Mon	2
	Thu	3
55076	Mon	4
	Wed	1
	Thu	1
55077	Mon	2
55082	Mon	2
	Wed	4
	Fri	2
55101	Mon	1
	Wed	2
	Thu	2
55102	Mon	3
	Wed	1
	Thu	4
	Fri	2
55104	Mon	2
	Wed	3
	Thu	2
55105	Mon	1
	Wed	1
	Thu	2
	Fri	1
55106	Wed	2
	Fri	1
55107	Mon	1
	Wed	1
55108	Mon	1
	Wed	2
55109	Wed	2
55110	Mon	1
	Wed	3
	Thu	1
	Fri	1
55112	Mon	4
	Wed	2
	Thu	1
55113	Mon	7
	Wed	5
	Thu	1
	Fri	1
55114	Mon	1
	Wed	1
55115	Mon	1
55116	Mon	1
	Wed	2
55117	Mon	3
	Wed	4
	Thu	1
55118	Mon	1
	Thu	1
55121	Mon	1
	Wed	1
	Thu	2
55122	Mon	1
	Wed	2
55124	Mon	1
	Wed	2
55125	Mon	2
	Wed	6
	Thu	3
55126	Mon	3
	Thu	1
55127	Mon	2
	Wed	4
55128	Mon	2
	Wed	2
55129	Wed	1
55130	Mon	1
	Wed	3
	Thu	1
55301	Wed	1
	Thu	1
55303	Mon	3
	Wed	2
55304	Mon	3
	Wed	4
55305	Mon	1
	Wed	2
	Thu	2
	Fri	1
55311	Mon	2
55315	Mon	1
55317	Wed	2
55318	Wed	1
55330	Mon	1
	Wed	2
	Fri	1
55331	Mon	2
	Wed	1
	Thu	1
55337	Mon	4
	Wed	4
	Thu	1
	Fri	1
55340	Wed	4
55343	Mon	6
	Wed	5
	Fri	1
55344	Mon	2
	Wed	3
	Thu	3
55346	Mon	1
55347	Mon	4
	Wed	1
55356	Wed	1
	Thu	1
55357	Mon	1
	Wed	1
55359	Mon	1
55369	Mon	8
	Wed	5
	Thu	5
55372	Mon	1
	Thu	1
55374	Wed	1
	Fri	1
55378	Mon	2
	Wed	3
55379	Mon	2
	Wed	2
	Thu	2
55391	Mon	2
55401	Mon	3
	Wed	3
	Thu	1
55402	Mon	1
	Wed	7
55403	Wed	2
	Thu	2
55404	Mon	2
	Wed	2
	Thu	1
55405	Mon	1
	Thu	1
55406	Mon	1
	Wed	7
	Thu	1
55407	Thu	1
55408	Mon	4
	Wed	5
	Thu	1
55410	Wed	2
55411	Mon	3
	Thu	5
55412	Mon	1
	Thu	1
55413	Mon	1
	Wed	3
	Thu	1
55414	Mon	5
	Wed	5
55415	Wed	1
55416	Mon	1
	Wed	3
55417	Mon	1
	Wed	1
55418	Mon	1
	Wed	3
55419	Mon	2
	Wed	1
	Thu	1
55420	Mon	1
	Wed	4
	Thu	1
	Sat	1
55421	Sun	1
	Mon	1
	Wed	2
55422	Wed	1
55423	Mon	4
	Wed	4
55424	Wed	1
55425	Sun	1
55426	Sun	1
	Mon	2
	Wed	1
	Thu	1
55427	Wed	2
	Thu	6
55428	Mon	2
	Wed	6
	Thu	1
55429	Mon	1
	Wed	2
55430	Mon	1
55431	Mon	1
	Wed	2
	Thu	3
	Fri	1
55432	Mon	1
	Wed	4
55433	Wed	1
	Fri	1
55434	Mon	1
	Wed	1
	Fri	1
55435	Mon	3
	Wed	3
	Thu	1
55437	Mon	1
	Wed	1
	Fri	1
55438	Mon	2
55439	Mon	5
	Wed	1
55442	Mon	1
	Wed	1
55443	Mon	2
	Wed	2
55445	Mon	2
	Wed	1
	Fri	3
55446	Mon	1
55447	Mon	2
	Wed	1
55448	Mon	1
	Wed	3
	Thu	1
	Fri	1
55449	Mon	6
	Wed	3
55454	Fri	1
54016	Mon	4
	Tue	5
	Wed	3
	Thu	2
54022	Thu	1
55003	Thu	1
55005	Mon	1
	Tue	2
	Wed	1
55014	Mon	1
	Tue	6
	Wed	2
	Thu	4
	Fri	3
55016	Mon	4
	Tue	2
	Wed	6
	Fri	1
55024	Mon	4
	Wed	2
	Fri	1
55025	Mon	1
	Tue	2
	Wed	4
	Thu	1
55033	Mon	1
	Tue	3
	Wed	3
	Thu	3
	Fri	1
55038	Mon	5
	Tue	2
	Wed	1
	Thu	3
	Fri	1
55042	Thu	1
	Fri	1
55043	Tue	7
55044	Mon	2
	Tue	7
	Wed	13
	Thu	3
55055	Tue	2
	Wed	3
	Thu	1
55068	Tue	4
	Wed	10
55071	Wed	2
	Thu	1
55075	Mon	5
	Tue	9
	Wed	1
	Thu	4
	Fri	1
55076	Mon	16
	Tue	15
	Wed	3
	Thu	1
55077	Mon	6
	Wed	1
55082	Mon	12
	Tue	8
	Wed	13
	Thu	2
	Fri	1
55101	Mon	14
	Tue	7
	Wed	9
	Thu	5
	Fri	4
55102	Mon	11
	Tue	5
	Wed	6
	Thu	6
	Fri	1
55103	Mon	2
	Tue	3
	Wed	1
	Thu	1
55104	Mon	15
	Tue	21
	Wed	15
	Thu	6
	Fri	5
55105	Mon	3
	Tue	9
	Wed	8
	Thu	3
	Fri	4
55106	Mon	2
	Tue	6
	Wed	7
	Thu	1
55107	Mon	1
	Tue	5
	Wed	3
	Fri	1
55108	Mon	8
	Tue	8
	Wed	1
	Thu	5
	Fri	1
55109	Mon	14
	Tue	7
	Wed	6
	Thu	1
	Fri	1
55110	Mon	3
	Tue	11
	Wed	11
	Thu	3
	Fri	4
55112	Mon	10
	Tue	13
	Wed	8
	Thu	2
	Fri	2
55113	Mon	19
	Tue	11
	Wed	19
	Thu	12
	Fri	3
55114	Mon	3
	Tue	11
	Wed	3
55115	Mon	1
	Wed	1
55116	Mon	6
	Tue	4
	Wed	15
	Thu	1
	Fri	2
55117	Mon	7
	Tue	9
	Wed	23
	Thu	4
55118	Mon	1
	Wed	6
	Thu	2
55119	Mon	5
	Tue	4
	Wed	6
	Thu	3
55120	Wed	1
	Fri	2
55121	Mon	3
	Tue	22
	Wed	8
	Thu	3
55122	Mon	5
	Tue	2
	Wed	8
	Thu	3
55123	Mon	2
55124	Mon	6
	Tue	7
	Wed	10
	Thu	2
55125	Mon	2
	Tue	10
	Wed	11
	Thu	4
	Fri	2
55126	Mon	4
	Tue	3
	Wed	2
	Fri	1
55127	Mon	1
	Tue	4
	Wed	8
	Fri	1
55128	Mon	10
	Tue	8
	Wed	9
	Thu	1
	Fri	2
55129	Tue	1
	Wed	6
	Thu	2
	Fri	1
55130	Mon	3
	Tue	7
	Wed	6
55150	Mon	1
	Tue	1
55301	Tue	4
	Thu	2
55303	Mon	16
	Tue	5
	Wed	10
	Thu	1
	Fri	1
55304	Mon	7
	Tue	4
	Wed	11
	Thu	1
	Fri	3
55305	Mon	6
	Tue	10
	Wed	8
	Thu	1
	Fri	4
55306	Mon	1
	Tue	1
	Wed	2
	Thu	2
55311	Mon	4
	Tue	1
	Wed	2
	Thu	2
	Fri	3
55315	Mon	1
55316	Mon	2
	Tue	1
	Wed	4
	Fri	2
55317	Mon	4
	Tue	9
	Wed	8
	Thu	1
	Fri	4
55318	Mon	6
	Tue	4
	Wed	3
	Thu	3
55328	Mon	1
55330	Mon	6
	Tue	5
	Wed	4
55331	Mon	9
	Tue	13
	Wed	17
55337	Mon	12
	Tue	18
	Wed	8
	Thu	6
	Fri	1
55340	Mon	6
	Tue	2
	Wed	4
	Thu	1
	Fri	1
55343	Mon	16
	Tue	15
	Wed	25
	Thu	2
	Sat	1
55344	Mon	10
	Tue	18
	Wed	7
	Thu	4
	Fri	1
55345	Mon	2
	Tue	7
	Wed	6
	Thu	1
55346	Mon	3
	Tue	6
55347	Mon	5
	Tue	10
	Wed	4
	Thu	2
	Fri	1
55356	Mon	5
	Tue	4
	Wed	2
	Thu	4
55357	Mon	3
	Thu	1
55359	Mon	5
	Wed	2
55364	Tue	1
	Wed	1
	Thu	1
55369	Mon	24
	Tue	15
	Wed	12
	Thu	10
	Fri	3
55372	Mon	9
	Tue	4
	Wed	8
	Thu	4
55374	Mon	6
	Tue	8
	Wed	8
	Fri	1
55375	Wed	1
55378	Mon	5
	Tue	7
	Wed	5
	Thu	2
55379	Mon	8
	Tue	8
	Wed	9
	Thu	3
	Fri	4
55384	Mon	2
	Tue	2
55386	Thu	2
55391	Mon	11
	Tue	11
	Wed	11
	Thu	4
	Fri	1
55401	Mon	12
	Tue	18
	Wed	10
	Thu	2
	Fri	3
55402	Mon	25
	Tue	19
	Wed	13
	Thu	9
	Fri	2
55403	Mon	4
	Tue	9
	Wed	9
	Thu	5
	Fri	4
55404	Mon	10
	Tue	9
	Wed	11
	Thu	1
	Fri	1
55405	Mon	3
	Tue	3
	Wed	2
	Thu	2
55406	Mon	9
	Tue	16
	Wed	22
	Thu	5
	Fri	5
55407	Mon	1
	Tue	8
	Wed	2
	Thu	8
	Fri	7
55408	Mon	14
	Tue	8
	Wed	12
	Thu	1
	Fri	2
55410	Mon	1
	Tue	2
	Wed	5
	Thu	4
55411	Mon	13
	Tue	9
	Wed	1
	Thu	12
	Fri	1
55412	Mon	2
	Tue	3
	Wed	1
55413	Mon	8
	Tue	101
	Wed	8
	Thu	6
55414	Mon	10
	Tue	13
	Wed	15
	Thu	5
55415	Tue	4
	Thu	1
	Fri	1
55416	Mon	14
	Tue	22
	Wed	17
	Thu	11
	Fri	10
55417	Mon	1
	Tue	1
	Wed	2
	Fri	1
55418	Mon	6
	Tue	12
	Wed	12
	Thu	2
55419	Mon	4
	Wed	2
	Thu	4
	Fri	2
55420	Mon	7
	Tue	14
	Wed	13
	Thu	4
	Fri	2
55421	Mon	2
	Tue	5
	Wed	6
	Thu	2
55422	Mon	4
	Tue	3
	Wed	4
	Thu	2
55423	Mon	6
	Tue	2
	Wed	10
	Thu	2
55424	Mon	1
	Tue	3
	Wed	4
55425	Mon	4
	Wed	3
	Fri	3
55426	Mon	18
	Tue	9
	Wed	7
	Thu	3
	Fri	3
55427	Mon	4
	Tue	6
	Wed	11
	Thu	2
	Fri	5
55428	Mon	10
	Tue	15
	Wed	18
	Thu	3
	Fri	2
55429	Mon	10
	Tue	4
	Wed	11
	Fri	1
55430	Mon	9
	Tue	8
	Wed	2
	Fri	1
55431	Mon	10
	Tue	5
	Wed	7
	Thu	1
	Fri	6
55432	Mon	6
	Tue	12
	Wed	17
	Thu	1
	Fri	1
55433	Mon	2
	Tue	8
	Wed	6
55434	Tue	4
	Wed	3
	Thu	3
	Fri	1
55435	Mon	14
	Tue	8
	Wed	7
	Thu	1
55436	Mon	3
	Tue	4
	Wed	3
	Fri	1
55437	Mon	4
	Tue	2
	Wed	2
	Fri	1
55438	Mon	7
	Tue	3
	Fri	1
55439	Mon	29
	Tue	22
	Wed	8
	Thu	2
55441	Mon	11
	Tue	10
	Wed	5
	Thu	4
55442	Mon	3
	Tue	1
	Wed	1
	Thu	1
55443	Mon	2
	Tue	1
	Wed	6
	Thu	1
55445	Mon	8
	Tue	1
	Wed	4
	Thu	2
55446	Mon	4
	Tue	3
	Wed	2
	Thu	1
55447	Mon	13
	Tue	7
	Wed	1
	Thu	1
	Fri	2
55448	Mon	2
	Tue	4
	Wed	4
	Thu	2
	Fri	3
55449	Mon	15
	Tue	20
	Wed	12
55454	Tue	1
	Wed	1

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Oeldere]

It seems to me that the duplicated zip codes are formatted as text (they are lined up on the left side).

That could (will) be the reason they get duplicated.

Format the cells in the Original data to values and make a new pivot table.

 

[Tiffgringa]

No, it's formatted as a number. I redid the pivot, using column headers for the zip, and still got duplicates.
There are duplicates in the master data, but I want to sum them in the pivot.

Count of DAY	ZIP
DAY	54016	54022	55003	55005	55014	55016	55024	55025	55033	55038	55044	55068	55075	55076	55077	55082	55101	55102	55104	55105	55106	55107	55108	55109	55110	55112	55113	55114	55115	55116	55117	55118	55121	55122	55124	55125	55126	55127	55128	55129	55130	55301	55303	55304	55305	55311	55315	55317	55318	55330	55331	55337	55340	55343	55344	55346	55347	55356	55357	55359	55369	55372	55374	55378	55379	55391	55401	55402	55403	55404	55405	55406	55407	55408	55410	55411	55412	55413	55414	55415	55416	55417	55418	55419	55420	55421	55422	55423	55424	55425	55426	55427	55428	55429	55430	55431	55432	55433	55434	55435	55437	55438	55439	55442	55443	55445	55446	55447	55448	55449	55454	54016	54022	55003	55005	55014	55016	55024	55025	55033	55038	55042	55043	55044	55055	55068	55071	55075	55076	55077	55082	55101	55102	55103	55104	55105	55106	55107	55108	55109	55110	55112	55113	55114	55115	55116	55117	55118	55119	55120	55121	55122	55123	55124	55125	55126	55127	55128	55129	55130	55150	55301	55303	55304	55305	55306	55311	55315	55316	55317	55318	55328	55330	55331	55337	55340	55343	55344	55345	55346	55347	55356	55357	55359	55364	55369	55372	55374	55375	55378	55379	55384	55386	55391	55401	55402	55403	55404	55405	55406	55407	55408	55410	55411	55412	55413	55414	55415	55416	55417	55418	55419	55420	55421	55422	55423	55424	55425	55426	55427	55428	55429	55430	55431	55432	55433	55434	55435	55436	55437	55438	55439	55441	55442	55443	55445	55446	55447	55448	55449	55454	Grand Total
Sun					4										2					5						7											4					2					1							12					2					5								9						5							7							8							3										7							16						2								7						29						35							18							10						7						1									61						4		3					20										59						125							42							28							11						30						15			606
Mon																					3						14											6												4		10			8							2			6								1						10							4							9							7				2						9													7				3										16						65							3							15												10				16		45			40									26				32		2								26						44							15							49							32						8						47		596
Tue		3				2					4											2						2					4						4				5					2			4									1							7							10						1							1							3											4								1				13							27														10						17							37							30					33							26				39					16					8											46							40						6							13							26							11						10							468
Wed	7		1				1			2							5						3						1					3						1				7		2			1							1							2					8													4							8							1						3											15		1				7			14											40							23						2							19							10		2			28			14				16									9								26								68													74							20							20							9												478
Thu								2										10						2						3											5				6								4				5												4						2							2							1							7						2				6													9														32							29						28							2							16					29											15					24								1								32						13							5							8							31							11					15						361
Fri				1					3			4	5			8									6						8				3																										18									5						8							4							1							5						6				1								4					12						14		23			36			7							34						43							25												6																				68												33						39							32							10							38							62					10					582
Sat														6					7													2				11																						2								2					2						2							4							5							2										4			1														6				36						65													10							30																	1										16										4		38					11						7							13							42							16												24			2	371
Grand Total	7	3	1	1	4	2	1	2	3	2	4	4	5	6	2	8	5	10	7	5	3	2	3	2	6	7	14	2	1	3	8	2	4	3	3	11	4	6	4	1	5	2	5	7	6	2	1	2	1	4	4	10	4	12	8	1	5	2	2	1	18	2	2	5	6	2	7	8	4	5	2	9	1	10	2	8	2	5	10	1	4	2	4	4	7	4	1	8	1	1	5	8	9	3	1	7	5	2	3	7	3	2	6	2	4	6	1	4	7	9	1	15	1	1	4	16	13	7	9	12	14	2	7	27	6	14	3	23	36	7	36	40	32	7	65	29	16	10	23	29	34	35	65	17	2	28	43	10	18	3	37	19	2	25	30	10	15	30	10	16	2	7	33	28	29	6	14	1	10	26	16	1	16	39	45	15	61	40	16	9	24	16	4	8	3	68	26	26	1	20	32	4	2	38	46	68	32	33	11	59	26	40	13	39	7	125	44	6	74	5	32	13	42	15	13	20	8	10	42	28	49	26	20	31	38	16	11	32	11	9	11	62	30	8	10	15	10	24	15	47	2	3462

<colgroup><col><col span="241"><col></colgroup><tbody>
</tbody>

 

[Oeldere]

probably they "look" like formatted values.

in the Original data multiply the cells with 1 (*1).

See after that if the values are lined up to the right.