Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with same data
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with same data
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. -- Regards, OssieMac "pulling my hair out" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with same data
I will give it a shot... thanks!!!
"OssieMac" wrote: 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. -- Regards, OssieMac "pulling my hair out" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Cells with same data
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. "pulling my hair out" wrote: I will give it a shot... thanks!!! "OssieMac" wrote: 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. -- Regards, OssieMac "pulling my hair out" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting cells with multiple data | Excel Worksheet Functions | |||
Counting cells that contain data | Excel Discussion (Misc queries) | |||
Counting cells with data | Excel Discussion (Misc queries) | |||
Complicated counting of cells (based on other cells contents) | Excel Worksheet Functions | |||
counting cells in a data range that meet 3 specific conditions | Excel Discussion (Misc queries) |