Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tallying Lists
I have a worksheet containing three columns of data representing a list of
materials and quantities. The first column contains a Part Number, the second column is a Part Number Name, and the third column is Quantity. Part Numbers (and their associated names) repeat through out this listing. I need to query the worksheet to determine what the total quantity of each Part Number is, and on a second worksheet display in 3 columns; Part Number; Part Number Name; and Total Quantity (where Total Quantity equally the sum of all the same Part Number quantities). On the second worksheet only one instance of each Part Number & Part Number Name should appear. Any help would be appreciated. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tallying Lists
Mike,
Use Subtotals from the Data menu can subtotal all of your part number quantities. It does required that the list be sorted. Once sub-totaled, you can copy the visible cells to another sheet. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Mike" wrote in message I have a worksheet containing three columns of data representing a list of materials and quantities. The first column contains a Part Number, the second column is a Part Number Name, and the third column is Quantity. Part Numbers (and their associated names) repeat through out this listing. I need to query the worksheet to determine what the total quantity of each Part Number is, and on a second worksheet display in 3 columns; Part Number; Part Number Name; and Total Quantity (where Total Quantity equally the sum of all the same Part Number quantities). On the second worksheet only one instance of each Part Number & Part Number Name should appear. Any help would be appreciated. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tallying Lists
Thanks, but what I was looking for was a formula I could put in the second
worksheet that would automatically extract this data from the first worksheet and generate a summary report. Perhaps there is just no way to do this without manual intervention(?). "Jim Cone" wrote: Mike, Use Subtotals from the Data menu can subtotal all of your part number quantities. It does required that the list be sorted. Once sub-totaled, you can copy the visible cells to another sheet. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Mike" wrote in message I have a worksheet containing three columns of data representing a list of materials and quantities. The first column contains a Part Number, the second column is a Part Number Name, and the third column is Quantity. Part Numbers (and their associated names) repeat through out this listing. I need to query the worksheet to determine what the total quantity of each Part Number is, and on a second worksheet display in 3 columns; Part Number; Part Number Name; and Total Quantity (where Total Quantity equally the sum of all the same Part Number quantities). On the second worksheet only one instance of each Part Number & Part Number Name should appear. Any help would be appreciated. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tallying Lists
It could all be done with some VBA code.
I doubt if it could be done using formulas. However, Max and Bob Phillips continue to amaze me with their formula solutions. Sorting, subtotaling and copying the results to a new sheet should only require 20 or 30 seconds for the whole procedure. -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "Mike" wrote in message Thanks, but what I was looking for was a formula I could put in the second worksheet that would automatically extract this data from the first worksheet and generate a summary report. Perhaps there is just no way to do this without manual intervention(?). "Jim Cone" wrote: Mike, Use Subtotals from the Data menu can subtotal all of your part number quantities. It does required that the list be sorted. Once sub-totaled, you can copy the visible cells to another sheet. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Mike" wrote in message I have a worksheet containing three columns of data representing a list of materials and quantities. The first column contains a Part Number, the second column is a Part Number Name, and the third column is Quantity. Part Numbers (and their associated names) repeat through out this listing. I need to query the worksheet to determine what the total quantity of each Part Number is, and on a second worksheet display in 3 columns; Part Number; Part Number Name; and Total Quantity (where Total Quantity equally the sum of all the same Part Number quantities). On the second worksheet only one instance of each Part Number & Part Number Name should appear. Any help would be appreciated. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tallying Lists
Have you tried the 'SUMIF' function? It may do the trick.
-- T Tipsy "Mike" wrote: I have a worksheet containing three columns of data representing a list of materials and quantities. The first column contains a Part Number, the second column is a Part Number Name, and the third column is Quantity. Part Numbers (and their associated names) repeat through out this listing. I need to query the worksheet to determine what the total quantity of each Part Number is, and on a second worksheet display in 3 columns; Part Number; Part Number Name; and Total Quantity (where Total Quantity equally the sum of all the same Part Number quantities). On the second worksheet only one instance of each Part Number & Part Number Name should appear. Any help would be appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Lists... | Excel Discussion (Misc queries) | |||
Lists... | Excel Discussion (Misc queries) | |||
VLOOKUP and Multi Lists | New Users to Excel | |||
Excel - need a function to compare lists | Excel Worksheet Functions |