If Column A has duplicates then add the value in column D for the duplicates

[EquipmentPartsSales]

I have a large inventory file I am working with. The data looks like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]NSN[/TD]
[TD]Part number[/TD]
[TD]Condition[/TD]
[TD]QTY[/TD]
[TD]DESC[/TD]
[/TR]
[TR]
[TD]1005001950897[/TD]
[TD]11662739[/TD]
[TD]NS[/TD]
[TD]7[/TD]
[TD]MOTOR[/TD]
[/TR]
[TR]
[TD]1005001950897[/TD]
[TD]11662739[/TD]
[TD]NS[/TD]
[TD]2[/TD]
[TD]MOTOR[/TD]
[/TR]
[TR]
[TD]1005002676740[/TD]
[TD]368506201[/TD]
[TD]NS[/TD]
[TD]4[/TD]
[TD]HOUSING[/TD]
[/TR]
[TR]
[TD]1005005013201[/TD]
[TD]5013201[/TD]
[TD]NS[/TD]
[TD]1[/TD]
[TD]SPRING[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Basically what I would like to do is if the number in column NSN has a duplicate then add up the values in the QTY column. For this example it would like like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]NSN[/TD]
[TD]Part number[/TD]
[TD]Condition[/TD]
[TD]QTY[/TD]
[TD]DESC[/TD]
[TD]Total Qty[/TD]
[/TR]
[TR]
[TD]1005001950897[/TD]
[TD]11662739[/TD]
[TD]NS[/TD]
[TD]7[/TD]
[TD]MOTOR[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]1005001950897[/TD]
[TD]11662739[/TD]
[TD]NS[/TD]
[TD]2[/TD]
[TD]MOTOR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1005002676740[/TD]
[TD]368506201[/TD]
[TD]NS[/TD]
[TD]4[/TD]
[TD]HOUSING[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1005005013201[/TD]
[TD]5013201[/TD]
[TD]NS[/TD]
[TD]1[/TD]
[TD]SPRING[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I do not know if there is a simple formula or a VBA code that will achieve this. My inventory list has roughly 200,000 lines. Any help would be greatly appreciated.

 

[Roderick_E]

=countif(A:A,A2)

Replace A with whatever column NSN is and A2 with whatever the first row of data is after column header.

 

[jtakw]

Hi,

Is this what you mean:

Book1
	A	B	C	D	E	F
1	NSN	Part number	Condition	QTY	DESC	Total Qty
2	1005001950897	11662739	NS	7	MOTOR	9
3	1005001950897	11662739	NS	2	MOTOR
4	1005002676740	368506201	NS	4	HOUSING
5	1005005013201	5013201	NS	1	SPRING
Sheet268

Cell Formulas
Range	Formula
F2	=IF(AND(COUNTIF(A$2:A2,A2)=1,COUNTIF(A$2:A$200000,A2)>1),SUMIF(A$2:A$200000,A2,D$2:D$200000),"")

Formula copied down.

 

[EquipmentPartsSales]

=countif(A:A,A2)

Replace A with whatever column NSN is and A2 with whatever the first row of data is after column header.

I found a VBA code as well that is currently running I will try this right after I did not want to drag the code down 200,000 lines. Thank you very much for your response it is greatly appreciated.

 

[Roderick_E]

Indeed, I missed the 200,000 lines part. Sorry about that.

 

[EquipmentPartsSales]

I tried this one first or at least something very similar. It puts the total qty in line with the lowest duplicate like this:

[TABLE="class: cms_table"]
<tbody>[TR]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]NSN[/TD]
[TD]Part number[/TD]
[TD]Condition[/TD]
[TD]QTY[/TD]
[TD]DESC[/TD]
[TD]Total Qty[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]1005001950897[/TD]
[TD="align: right"]11662739[/TD]
[TD]NS[/TD]
[TD="align: right"]7[/TD]
[TD]MOTOR[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]1005001950897[/TD]
[TD="align: right"]11662739[/TD]
[TD]NS[/TD]
[TD="align: right"]2[/TD]
[TD]MOTOR[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]1005002676740[/TD]
[TD="align: right"]368506201[/TD]
[TD]NS[/TD]
[TD="align: right"]4[/TD]
[TD]HOUSING[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]1005005013201[/TD]
[TD="align: right"]5013201[/TD]
[TD]NS[/TD]
[TD="align: right"]1[/TD]
[TD]SPRING

[/TD]
[/TR]
</tbody>[/TABLE]

The problem with that is when I use VBA to change the duplicates to blanks it only saves the first one. When I would go to delete the row I would lose the data.


Fixed the post

 

[EquipmentPartsSales]

Indeed, I missed the 200,000 lines part. Sorry about that.

Not a problem at all. I appreciate the input.

 

[jtakw]

I tried this one first or at least something very similar. It puts the total qty in line with the lowest duplicate like this:

[TABLE="class: cms_table"]
<tbody>[TR]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]NSN[/TD]
[TD]Part number[/TD]
[TD]Condition[/TD]
[TD]QTY[/TD]
[TD]DESC[/TD]
[TD]Total Qty[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]1005001950897[/TD]
[TD="align: right"]11662739[/TD]
[TD]NS[/TD]
[TD="align: right"]7[/TD]
[TD]MOTOR[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]1005001950897[/TD]
[TD="align: right"]11662739[/TD]
[TD]NS[/TD]
[TD="align: right"]2[/TD]
[TD]MOTOR[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]1005002676740[/TD]
[TD="align: right"]368506201[/TD]
[TD]NS[/TD]
[TD="align: right"]4[/TD]
[TD]HOUSING[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]1005005013201[/TD]
[TD="align: right"]5013201[/TD]
[TD]NS[/TD]
[TD="align: right"]1[/TD]
[TD]SPRING

[/TD]
[/TR]
</tbody>[/TABLE]

The problem with that is when I use VBA to change the duplicates to blanks it only saves the first one. When I would go to delete the row I would lose the data.


Fixed the post

I'm not understanding this comment, and don't know if you're referring to my Post # 3.

Also, if using a formula solution, there's no need to "physically" copy the formula down 200000 rows.

Let me know if my formula is doing what you need, and I'll respond accordingly.

 

[EquipmentPartsSales]

I'm not understanding this comment, and don't know if you're referring to my Post # 3.

Also, if using a formula solution, there's no need to "physically" copy the formula down 200000 rows.

Let me know if my formula is doing what you need, and I'll respond accordingly.

Not trying to sound rude I truly do not know how do you get the formula all the way down without dragging it?

 

[jtakw]

No, not sounding rude at all, I just didn't know who you were talking to, and still don't know if my formula is working for you.

The other formula offered above Only does a COUNT of Column A, whereas, according to your description you want a SUM of Column D (Qty) for Duplicates in Column A, but ignore the unique NSN(s) in Column A (as my formula does).

Anyway, using my table in Post # 3 as sample, to quickly copy F2 formula All the Way to F200000:

1. Select F2, Right click, Copy
2. Hit F5, inside the "Reference" box, type F3:F200000
3. Hold down Shift, Click "OK"
4. Control V, the formula is now copied All the way down to F200000