Merge Duplicates and add sum

[Zafar900]

Good day all,

I would like to know if anybody has a solution to this problem for me as i am unable to find another solution online

Part number	Quantity	Price DHS
W9Y0100K0C	1	2549.71
WL0112205	1	114.43
WL0112205	2	114.43
WL9118X10	1	130.85
WL9118X10	1	130.85
WL9118X10	1	130.85
WL9118X10	1	130.85
WL9118X10	1	130.85
WL0112205	1	114.43
W9Y111SC0	1	798.55
WL9118X10	3	130.85
WL9118X10	1	130.85
WL9118X10	1	130.85

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

I have An excel File similar to the one above and would like to know how to merge the duplicate part numbers and sum the qty of the merged duplicates and to keep the price the same as the merged duplicates,

sorry for the confusing question

thanks

 

[Mind The Gaap1]

Hi Zafar,

Hope I am understanding your question properly, but I think the answer is to just use a pivot table. Part number as the row label, quantity as the column label and sum the values of quantity.

Lucas

 

[63falcondude]

Refer to the example below. Start by copying column A to column E and remove duplicates. Then put in the formulas in columns F and G.

Excel 2010
	A	B	C	D	E	F	G
1	Part number	Quantity	Price DHS		Part number	Quantity	Price DHS
2	W9Y0100K0C	1	2549.71		W9Y0100K0C	1	2549.71
3	WL0112205	1	114.43		WL0112205	4	114.43
4	WL0112205	2	114.43		WL9118X10	10	130.85
5	WL9118X10	1	130.85		W9Y111SC0	1	798.55
6	WL9118X10	1	130.85
7	WL9118X10	1	130.85
8	WL9118X10	1	130.85
9	WL9118X10	1	130.85
10	WL0112205	1	114.43
11	W9Y111SC0	1	798.55
12	WL9118X10	3	130.85
13	WL9118X10	1	130.85
14	WL9118X10	1	130.85
Sheet1

Cell Formulas
Range	Formula
F2	=SUMIF(A:A,E2,B:B)
G2	=INDEX(C:C,MATCH(E2,A:A,0))