Subtotals

[Josh]

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?

[R. Choate]

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" <[email protected]> wrote in message news:[email protected]...
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?

[Josh]

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" <[email protected]> wrote in message news:[email protected]...
> 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?
>
>
>

[R. Choate]

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" <[email protected]> wrote in message news:[email protected]...

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" <[email protected]> wrote in message news:[email protected]...
> 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?
>
>
>