Analyzing a sub-totalled list

[[email protected]]

I have a list of sub-totalled parts similar to this:

Part x Description 1 Used on Car 1
Part x Description 2 Used on Car 2
Part x Description 3 Used on Car 3
Part x Total 6

Part y Description 2 Used on Car 2
Part y Description 3 Used on Car 3
Part y Total 5

Part z Description 5 Used on Car 1
Part z Total 5

There are three different parts x, y and z which are used in differing
quantities on different models of cars.

I want to know:
a) how many parts are used only on car 1, only on car 2, etc.
b) how many parts are used on car 1 and car 2
c) how many parts are used on car 1, car 2 and car 3
etc.

for all of the various combinations.

Any ideas?

Garth

[Dave Peterson]

First, I'd remove the subtotals.

Then I'd use data|pivottable to come up with that summary report.

Add a single row of headers (if you don't have them now)
Select the whole range
Data|Pivottable
follow the wizard until you get to the step with a Layout button.
Click that button
Drag the Part number button to the row field
drag the car type button to the column field
drag the qty button to the data area
If you don't see "sum of", double click on it and choose sum of

And finish up the wizard.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

[email protected] wrote:
>
> I have a list of sub-totalled parts similar to this:
>
> Part x Description 1 Used on Car 1
> Part x Description 2 Used on Car 2
> Part x Description 3 Used on Car 3
> Part x Total 6
>
> Part y Description 2 Used on Car 2
> Part y Description 3 Used on Car 3
> Part y Total 5
>
> Part z Description 5 Used on Car 1
> Part z Total 5
>
> There are three different parts x, y and z which are used in differing
> quantities on different models of cars.
>
> I want to know:
> a) how many parts are used only on car 1, only on car 2, etc.
> b) how many parts are used on car 1 and car 2
> c) how many parts are used on car 1, car 2 and car 3
> etc.
>
> for all of the various combinations.
>
> Any ideas?
>
> Garth

--

Dave Peterson

[[email protected]]

Thanks very much. Your post worked perfectly. I hadn't ever used
Pivot Tables before so I'm glad I got to learn about them.


Dave Peterson wrote:
> First, I'd remove the subtotals.
>
> Then I'd use data|pivottable to come up with that summary report.
>
> Add a single row of headers (if you don't have them now)
> Select the whole range
> Data|Pivottable
> follow the wizard until you get to the step with a Layout button.
> Click that button
> Drag the Part number button to the row field
> drag the car type button to the column field
> drag the qty button to the data area
> If you don't see "sum of", double click on it and choose sum of
>
> And finish up the wizard.
>
> If you want to read more about pivottables...
>
> Here are a few links:
>
> Debra Dalgleish's pictures at Jon Peltier's site:
> http://peltiertech.com/Excel/Pivots/pivottables.htm
> And Debra's own site:
> http://www.contextures.com/xlPivot01.html
>
> John Walkenbach also has some at:
> http://j-walk.com/ss/excel/files/general.htm
> (look for Tony Gwynn's Hit Database)
>
> Chip Pearson keeps Harald Staff's notes at:
> http://www.cpearson.com/excel/pivots.htm
>
> MS has some at (xl2000 and xl2002):
> http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
> http://office.microsoft.com/assistan...lconPT101.aspx
>

[Dave Peterson]

Debra Dalgleish has lots of notes at her site about Pivottables.

And she's written a book, too!

http://www.amazon.com/gp/product/159...91037?n=283155

You may want to look at it at your local bookstore to see if you should own it.

[email protected] wrote:
>
> Thanks very much. Your post worked perfectly. I hadn't ever used
> Pivot Tables before so I'm glad I got to learn about them.
>
> Dave Peterson wrote:
> > First, I'd remove the subtotals.
> >
> > Then I'd use data|pivottable to come up with that summary report.
> >
> > Add a single row of headers (if you don't have them now)
> > Select the whole range
> > Data|Pivottable
> > follow the wizard until you get to the step with a Layout button.
> > Click that button
> > Drag the Part number button to the row field
> > drag the car type button to the column field
> > drag the qty button to the data area
> > If you don't see "sum of", double click on it and choose sum of
> >
> > And finish up the wizard.
> >
> > If you want to read more about pivottables...
> >
> > Here are a few links:
> >
> > Debra Dalgleish's pictures at Jon Peltier's site:
> > http://peltiertech.com/Excel/Pivots/pivottables.htm
> > And Debra's own site:
> > http://www.contextures.com/xlPivot01.html
> >
> > John Walkenbach also has some at:
> > http://j-walk.com/ss/excel/files/general.htm
> > (look for Tony Gwynn's Hit Database)
> >
> > Chip Pearson keeps Harald Staff's notes at:
> > http://www.cpearson.com/excel/pivots.htm
> >
> > MS has some at (xl2000 and xl2002):
> > http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
> > http://office.microsoft.com/assistan...lconPT101.aspx
> >

--

Dave Peterson