Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
I have a list of data consisting of part#'s, vendor, qty, etc...
I would like to total the qty for each part#, and then delete all the other lines so I have a collapsed list; where each part# is listed once, with the total. The subtotal function gives me the totals I need but then gets in the way for other data manipultion needs. Is there a way to SUM the Qty column for rows w/ the same Part# then delete all the rows where the Part# is duplicated? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
It sounds to me like you would be better off using the advanced filter. There you can specify criteria that will cause only certain
rows to be displayed. It won't delete anything, but I believe it allows you to build formulas that only operate on the displayed rows. Advanced filter also allows you to copy the unique items to another area of your worksheet if that helps. Then you could filter those rows or build formulas there. Another solution for you would be to just use a simple pivot table. That would let you pretty much do whatever you want, relative to your description of your problem. My only work of caution with pivot tables is that they can be tricky if you create external formulas which reference values in the table. They also take a little time to get used to, but I think you would find one very helpful for your situation. HTH -- RMC,CPA "Josh" wrote in message ... I have a list of data consisting of part#'s, vendor, qty, etc... I would like to total the qty for each part#, and then delete all the other lines so I have a collapsed list; where each part# is listed once, with the total. The subtotal function gives me the totals I need but then gets in the way for other data manipultion needs. Is there a way to SUM the Qty column for rows w/ the same Part# then delete all the rows where the Part# is duplicated? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
Once I extract the totals and remove the duplicate rows I pass the info to someone else to do procurment and tracking & then they forward it back when they are done. I'm sceptical that the advanced filter will work for me. I'm afraid it will just add confusion since multiple people use the data. Josh "R. Choate" wrote: It sounds to me like you would be better off using the advanced filter. There you can specify criteria that will cause only certain rows to be displayed. It won't delete anything, but I believe it allows you to build formulas that only operate on the displayed rows. Advanced filter also allows you to copy the unique items to another area of your worksheet if that helps. Then you could filter those rows or build formulas there. Another solution for you would be to just use a simple pivot table. That would let you pretty much do whatever you want, relative to your description of your problem. My only work of caution with pivot tables is that they can be tricky if you create external formulas which reference values in the table. They also take a little time to get used to, but I think you would find one very helpful for your situation. HTH -- RMC,CPA "Josh" wrote in message ... I have a list of data consisting of part#'s, vendor, qty, etc... I would like to total the qty for each part#, and then delete all the other lines so I have a collapsed list; where each part# is listed once, with the total. The subtotal function gives me the totals I need but then gets in the way for other data manipultion needs. Is there a way to SUM the Qty column for rows w/ the same Part# then delete all the rows where the Part# is duplicated? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
I think most of the solutions to your issue would be subject to confusion and problems under the conditions you describe. Sometimes,
making something work is not too hard but making it bulletproof and user-friendly for other users downstream is another problem altogether. I don't have such a solution for you. Perhaps another person might be able to help. If you don't get help within a day or so, you might re-post your problem and include the details about the other people who will work on the file that you don't want to confuse. That will possibly affect the type of answer you get. I'm sorry I couldn't help you further. -- RMC,CPA "Josh" wrote in message ... Once I extract the totals and remove the duplicate rows I pass the info to someone else to do procurment and tracking & then they forward it back when they are done. I'm sceptical that the advanced filter will work for me. I'm afraid it will just add confusion since multiple people use the data. Josh "R. Choate" wrote: It sounds to me like you would be better off using the advanced filter. There you can specify criteria that will cause only certain rows to be displayed. It won't delete anything, but I believe it allows you to build formulas that only operate on the displayed rows. Advanced filter also allows you to copy the unique items to another area of your worksheet if that helps. Then you could filter those rows or build formulas there. Another solution for you would be to just use a simple pivot table. That would let you pretty much do whatever you want, relative to your description of your problem. My only work of caution with pivot tables is that they can be tricky if you create external formulas which reference values in the table. They also take a little time to get used to, but I think you would find one very helpful for your situation. HTH -- RMC,CPA "Josh" wrote in message ... I have a list of data consisting of part#'s, vendor, qty, etc... I would like to total the qty for each part#, and then delete all the other lines so I have a collapsed list; where each part# is listed once, with the total. The subtotal function gives me the totals I need but then gets in the way for other data manipultion needs. Is there a way to SUM the Qty column for rows w/ the same Part# then delete all the rows where the Part# is duplicated? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Presenting Subtotals only | Excel Discussion (Misc queries) | |||
Subtotals adding a description next to the subtotals | Excel Worksheet Functions | |||
Subtotals copy paste | Excel Worksheet Functions | |||
How do I copy an outline w/ subtotals & paste just the subtotals | Excel Discussion (Misc queries) | |||
Excel gets subtotals out of order using multiple sorts and subtot. | Excel Discussion (Misc queries) |