Counting Cells with same data

[Guest]

I have a worksheet with over 50,000 rows but only approximately 3,000 unique
rows. The rest are the same data with different qty or category, etc.
(Column 1 = Data, Column 2 = Category, Column 3 = qty)

Is there a way to filter the sheet by each unique cell, hide the duplicates,
and give a total of how many duplicates?

The columns consist of part #, qty sold, category, date, time... I just want
a total of each part number and how many were sold regardless of the
category, date, time.

Thanks,
Wayne

 

[Guest]

Hi Wayne,

A pivot table should do what you want. Look it up in help. However, if you
cannot work it out then get back to me and let me know what version of xl you
are using and I'll try to provide some instructions to step you through it.

As a starter:-

In xl 2007 select the Insert tab on the ribbon and then pivot table (far
left of ribbon)

In previous versions:-

Select menu item Data then Pivot table.....

Only select Part# and Qty sold columns of data where requested in the dialog
box and and simply follow the directions in the dialog boxes to complete.
Then drag the part# to the row headers down the left of the pivot table and
Qty sold into the data area to the right of the part#. there are options for
adding, average etc but I think that it will default to summing the qty sold.

 

[Guest]

I will give it a shot... thanks!!!

Hi Wayne,

A pivot table should do what you want. Look it up in help. However, if you
cannot work it out then get back to me and let me know what version of xl you
are using and I'll try to provide some instructions to step you through it.

As a starter:-

In xl 2007 select the Insert tab on the ribbon and then pivot table (far
left of ribbon)

In previous versions:-

Select menu item Data then Pivot table.....

Only select Part# and Qty sold columns of data where requested in the dialog
box and and simply follow the directions in the dialog boxes to complete.
Then drag the part# to the row headers down the left of the pivot table and
Qty sold into the data area to the right of the part#. there are options for
adding, average etc but I think that it will default to summing the qty sold.

 

[Guest]

Have you tried Subtotals in the Data menu?

For each change in Part #, sum the Quantity. Check Summary Below Data.
Then return to Data | Subtotals and COUNT the Part #.
UNCHECK replace previous subtotals and UNCHECK Quantity.

This may be done in either order.
If you click on the 2 or 3 in the left pane, the sheet will collapse and you
will only see the total quantity or the total quantity and the number of
duplicates for each part number.