Subtotals

[Dave]

I have a rather large spreadsheet (~12000 entries) which contains inventory
data. I have many parts which are in multiple location, but I just want a
subtotal by part number of the quantities in all locations.

I tried subtotal, and the sheet shows the subtotals, but it opens up an
outline view or something to that effect, and I want just the summary
information on that sheet or be able to copy to another worksheet. But no
matter what I do, I either get a grand total entry or all entries, including
each location. How do I obtain just the subtotal data where I can perform
further functions like VLOOKUP (for another sheet).

I tried Pivot Table but it comes back and says something like too many items.

I'll readily admit I'm an amateur at subtotals. Can anyone help?

Thanks in advance,

Dave

[Excelenator]

Check out the DSUM function if you want only a total on a summary sheet. Also you could probably use SUMPRODUCT.

[Dave Peterson]

You could use the outlining symbols at the left to hide the details.

Then select the range to copy
edit|goto special|visible cells
edit|copy
and paste to your new location.

One more Edit|Replace to remove the " totals" from the key column (if you want).

Record a macro when you do this and you'll have some code you can tweak.

Dave wrote:
>
> I have a rather large spreadsheet (~12000 entries) which contains inventory
> data. I have many parts which are in multiple location, but I just want a
> subtotal by part number of the quantities in all locations.
>
> I tried subtotal, and the sheet shows the subtotals, but it opens up an
> outline view or something to that effect, and I want just the summary
> information on that sheet or be able to copy to another worksheet. But no
> matter what I do, I either get a grand total entry or all entries, including
> each location. How do I obtain just the subtotal data where I can perform
> further functions like VLOOKUP (for another sheet).
>
> I tried Pivot Table but it comes back and says something like too many items.
>
> I'll readily admit I'm an amateur at subtotals. Can anyone help?
>
> Thanks in advance,
>
> Dave

--

Dave Peterson

[Jim May]

You can use the Subtotaled Sheet as a basis for getting what you want.
Click on the "2" level to see the Sub-total amounts only. Then Highlight the
area (including headers); With the area you want to copy Selected - at the
Menu, select Edit, GoTo... Click the Speicail.. button and on the resulting
dialog box look on the right side near the bottom and check the Visible Cells
Only item. OK out

Now do a Copy, go to a new blank sheet and do Edit Paste-Special - Values
That should get you there.

HTH,
Jim

"Dave" wrote:

> I have a rather large spreadsheet (~12000 entries) which contains inventory
> data. I have many parts which are in multiple location, but I just want a
> subtotal by part number of the quantities in all locations.
>
> I tried subtotal, and the sheet shows the subtotals, but it opens up an
> outline view or something to that effect, and I want just the summary
> information on that sheet or be able to copy to another worksheet. But no
> matter what I do, I either get a grand total entry or all entries, including
> each location. How do I obtain just the subtotal data where I can perform
> further functions like VLOOKUP (for another sheet).
>
> I tried Pivot Table but it comes back and says something like too many items.
>
> I'll readily admit I'm an amateur at subtotals. Can anyone help?
>
> Thanks in advance,
>
> Dave

[Dave]

Thanks Dave, Jim, Excelenator. I managed to muddle through selecting the
level 2 info and pasting it into another sheet then converting to values.

That worked for what I need right now, but now I have another problem. I
use that data to do a vlookup on another spreadsheet (one of a BOM with
multiple instances of a part. I want to summarize the entire quantity per
for all occurances of a part to see if I have enough inventory on hand to
cover the requirements of that BOM. I can subtotal, the cell formula looks
like =SUBTOTAL(9,J35:J49) which is the correct range, but the resulting
subtotal is 0, even though the quantites are >0.

Is there something I'm missing?

Thanks,

Dave

"Jim May" wrote:

> You can use the Subtotaled Sheet as a basis for getting what you want.
> Click on the "2" level to see the Sub-total amounts only. Then Highlight the
> area (including headers); With the area you want to copy Selected - at the
> Menu, select Edit, GoTo... Click the Speicail.. button and on the resulting
> dialog box look on the right side near the bottom and check the Visible Cells
> Only item. OK out
>
> Now do a Copy, go to a new blank sheet and do Edit Paste-Special - Values
> That should get you there.
>
> HTH,
> Jim
>
> "Dave" wrote:
>
> > I have a rather large spreadsheet (~12000 entries) which contains inventory
> > data. I have many parts which are in multiple location, but I just want a
> > subtotal by part number of the quantities in all locations.
> >
> > I tried subtotal, and the sheet shows the subtotals, but it opens up an
> > outline view or something to that effect, and I want just the summary
> > information on that sheet or be able to copy to another worksheet. But no
> > matter what I do, I either get a grand total entry or all entries, including
> > each location. How do I obtain just the subtotal data where I can perform
> > further functions like VLOOKUP (for another sheet).
> >
> > I tried Pivot Table but it comes back and says something like too many items.
> >
> > I'll readily admit I'm an amateur at subtotals. Can anyone help?
> >
> > Thanks in advance,
> >
> > Dave

[Dave Peterson]

If your subtotal returns a 0, that means that the
sum is 0 or your values in that range aren't really numbers. They could be Text
values that are masquerading as numbers.

One way that may convert those text numbers to number numbers is to
select an empty cell
edit|copy
select the offending range (I'd do it before applying data|subtotals)
edit|Paste special|check Addition

If that doesn't help, maybe...

You've copied the values from a web page and your data has some of those HTML
non-breaking spaces in the cells.

David McRitchie has a macro that cleans up this kind of stuff:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()"

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



Dave wrote:
>
> Thanks Dave, Jim, Excelenator. I managed to muddle through selecting the
> level 2 info and pasting it into another sheet then converting to values.
>
> That worked for what I need right now, but now I have another problem. I
> use that data to do a vlookup on another spreadsheet (one of a BOM with
> multiple instances of a part. I want to summarize the entire quantity per
> for all occurances of a part to see if I have enough inventory on hand to
> cover the requirements of that BOM. I can subtotal, the cell formula looks
> like =SUBTOTAL(9,J35:J49) which is the correct range, but the resulting
> subtotal is 0, even though the quantites are >0.
>
> Is there something I'm missing?
>
> Thanks,
>
> Dave
>
> "Jim May" wrote:
>
> > You can use the Subtotaled Sheet as a basis for getting what you want.
> > Click on the "2" level to see the Sub-total amounts only. Then Highlight the
> > area (including headers); With the area you want to copy Selected - at the
> > Menu, select Edit, GoTo... Click the Speicail.. button and on the resulting
> > dialog box look on the right side near the bottom and check the Visible Cells
> > Only item. OK out
> >
> > Now do a Copy, go to a new blank sheet and do Edit Paste-Special - Values
> > That should get you there.
> >
> > HTH,
> > Jim
> >
> > "Dave" wrote:
> >
> > > I have a rather large spreadsheet (~12000 entries) which contains inventory
> > > data. I have many parts which are in multiple location, but I just want a
> > > subtotal by part number of the quantities in all locations.
> > >
> > > I tried subtotal, and the sheet shows the subtotals, but it opens up an
> > > outline view or something to that effect, and I want just the summary
> > > information on that sheet or be able to copy to another worksheet. But no
> > > matter what I do, I either get a grand total entry or all entries, including
> > > each location. How do I obtain just the subtotal data where I can perform
> > > further functions like VLOOKUP (for another sheet).
> > >
> > > I tried Pivot Table but it comes back and says something like too many items.
> > >
> > > I'll readily admit I'm an amateur at subtotals. Can anyone help?
> > >
> > > Thanks in advance,
> > >
> > > Dave

--

Dave Peterson