How many of each part number was sold?

**hbennick** · 02-21-2013, 07:54 PM

So here's the problem. I have a list of sales from last year. I need to find out how many of each part number was sold. The first column has part numbers, second has quantity. The part numbers may appear once, they may appear in 100 lines. There are 18000 lines or so total. How in the world do I do this?

Example:

Model # Qty
19A4GLTEFU 13
19A4GLTEFU 3
19A4GLTEFU 1
19A4GLTEFU 2
19A4GLTEFU 1
19GOBI2FU2 10
19GOBI2FU2 20
19GOBI2000FU 6
19GOBI2000FU 1
19GOBI2FU2 1
19GOBI2FU2 5
19GOBI2FU2 1
19GOBI2FU2 5
19GOBI2FU2 1
19GOBI2FU2 1
19GOBI2000FU 1
19GOBI2FU2 6
19GOBI2000FU 2
19GOBI2FU2 3
19GOBI2FU2 1
19GOBI2FU2 43
19GOBI2FU2 2
19GOBI2000FU 2
19GOBI2000FU 3
19GOBI2000FU 3
19GOBI2000FU 1

**Pete_UK** · 02-21-2013, 08:14 PM

You can put this array* formula in D2:

Formula:

Please Login or Register  to view this content.

Adjust the ranges to suit your data (might be a bit slow with 18000 rows) then copy down as far as you think you need it. This will generate a list of unique items from column A.

*NOTE that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>.

Then you can put this formula in E2:

Formula:

Please Login or Register  to view this content.

then copy that down, to get the totals for each unique item.

The attached file demonstrates this on your sample data.

Hope this helps.

Pete

**davesexcel** · 02-21-2013, 08:30 PM

Excel 2007 has a function that will remove duplicates.
This is found under the Data ribbon.

Copy the entire column to another column,select the new column and goto Data=>remove Duplicates.
All duplicate items will be removed.
Then you can use the sumif() formula to add the items.
=SUMIF(A:A,H2,B:B)

Checkout the simple example workbook. It is a macro enabled workbook, you will have to allow editing and whatever it asks you when you open the workbook.
Click the button to get the results.

**mehmetcik** · 02-21-2013, 08:58 PM

Ok

Try The Following

open the file

look at the data

press the button.

if you want to see how it works then

select developer at the top of excel

select the macto and select step into

minimise the visual basic window so you can see both it and the excel data

pressing f8 will step you through the macro.

Enjoy