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:

NSN	Part number	Condition	QTY	DESC
1005001950897	11662739	NS	7	MOTOR
1005001950897	11662739	NS	2	MOTOR
1005002676740	368506201	NS	4	HOUSING
1005005013201	5013201	NS	1	SPRING

<tbody>
</tbody>

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:

NSN	Part number	Condition	QTY	DESC	Total Qty
1005001950897	11662739	NS	7	MOTOR	9
1005001950897	11662739	NS	2	MOTOR
1005002676740	368506201	NS	4	HOUSING
1005005013201	5013201	NS	1	SPRING

<tbody>
</tbody>

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.

 

[Fluff]

Another way to copy a formula down, is select the cell with the formula & double click on the little ∎ in the bottom right of the cell. This will fill down to the last used cell in the column to the immediate right.

 

[jtakw]

Yes, if you don't have any blank rows in between your data, Fluff's suggestion is the quickest way.

 

[EquipmentPartsSales]

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

Thank you for all the info I had to to use a VBA module in order to make it work. I am saving your countif formula for other uses. Thanks again.