PivotTable Question

I have a PivotTable that contains a list of buyers, their suppliers, and part
numbers. When I move the buyer and the supplier to the rows and put the
suppliers also into the data area, I get a count of the parts for each
supplier. I assume this is because the PivotTable is counting how many
records contain that buyer,that supplier and the parts related to that
supplier. Is there a way to only count the number of suppliers?

Thank you.

You can create a new pivot table with suppliers in the row area and suppliers
in the data area, you will get a unique list of suppliers with the number of
instances each occurs in the data.

Does this help?

"j_cavanagh" wrote:

> I have a PivotTable that contains a list of buyers, their suppliers, and part
> numbers. When I move the buyer and the supplier to the rows and put the
> suppliers also into the data area, I get a count of the parts for each
> supplier. I assume this is because the PivotTable is counting how many
> records contain that buyer,that supplier and the parts related to that
> supplier. Is there a way to only count the number of suppliers?
>
> Thank you.

This does not work, because suppliers appear more than once because they have
more than one part associated with them. I would like to display each
supplier once, regardless of how many parts they supply.

Thanks.

"Dominic" wrote:

> You can create a new pivot table with suppliers in the row area and suppliers
> in the data area, you will get a unique list of suppliers with the number of
> instances each occurs in the data.
>
> Does this help?
>
> "j_cavanagh" wrote:
>
> > I have a PivotTable that contains a list of buyers, their suppliers, and part
> > numbers. When I move the buyer and the supplier to the rows and put the
> > suppliers also into the data area, I get a count of the parts for each
> > supplier. I assume this is because the PivotTable is counting how many
> > records contain that buyer,that supplier and the parts related to that
> > supplier. Is there a way to only count the number of suppliers?
> >
> > Thank you.

This should display each supplier one time in the rows area.

How is your data organized?

Does your pivot table have ONLY supplier name in the row area?

"j_cavanagh" wrote:

> This does not work, because suppliers appear more than once because they have
> more than one part associated with them. I would like to display each
> supplier once, regardless of how many parts they supply.
>
> Thanks.
>
> "Dominic" wrote:
>
> > You can create a new pivot table with suppliers in the row area and suppliers
> > in the data area, you will get a unique list of suppliers with the number of
> > instances each occurs in the data.
> >
> > Does this help?
> >
> > "j_cavanagh" wrote:
> >
> > > I have a PivotTable that contains a list of buyers, their suppliers, and part
> > > numbers. When I move the buyer and the supplier to the rows and put the
> > > suppliers also into the data area, I get a count of the parts for each
> > > supplier. I assume this is because the PivotTable is counting how many
> > > records contain that buyer,that supplier and the parts related to that
> > > supplier. Is there a way to only count the number of suppliers?
> > >
> > > Thank you.

The data fields are buyer, supplier, and part number.

Buyer and supplier are in the row area. Supplier is in the data area.



"Dominic" wrote:

> This should display each supplier one time in the rows area.
>
> How is your data organized?
>
> Does your pivot table have ONLY supplier name in the row area?
>
> "j_cavanagh" wrote:
>
> > This does not work, because suppliers appear more than once because they have
> > more than one part associated with them. I would like to display each
> > supplier once, regardless of how many parts they supply.
> >
> > Thanks.
> >
> > "Dominic" wrote:
> >
> > > You can create a new pivot table with suppliers in the row area and suppliers
> > > in the data area, you will get a unique list of suppliers with the number of
> > > instances each occurs in the data.
> > >
> > > Does this help?
> > >
> > > "j_cavanagh" wrote:
> > >
> > > > I have a PivotTable that contains a list of buyers, their suppliers, and part
> > > > numbers. When I move the buyer and the supplier to the rows and put the
> > > > suppliers also into the data area, I get a count of the parts for each
> > > > supplier. I assume this is because the PivotTable is counting how many
> > > > records contain that buyer,that supplier and the parts related to that
> > > > supplier. Is there a way to only count the number of suppliers?
> > > >
> > > > Thank you.

In order to get a list of just suppliers, you need to have only suppliers in
the row area. Otherwise, it will group supplier by buyer. So, unless you need
the buyer info for this particular purpose, leave buyer out of the pivot
table and only put supplier in the row area. This will give you a list of
unique suppliers.

If you need the buyer info, you could put the supplier and buyer in the row
area, just make sure that supplier comes first (Top in Layout view, left-most
in the pivot table view).

Does that help?





"j_cavanagh" wrote:

> The data fields are buyer, supplier, and part number.
>
> Buyer and supplier are in the row area. Supplier is in the data area.
>
>
>
> "Dominic" wrote:
>
> > This should display each supplier one time in the rows area.
> >
> > How is your data organized?
> >
> > Does your pivot table have ONLY supplier name in the row area?
> >
> > "j_cavanagh" wrote:
> >
> > > This does not work, because suppliers appear more than once because they have
> > > more than one part associated with them. I would like to display each
> > > supplier once, regardless of how many parts they supply.
> > >
> > > Thanks.
> > >
> > > "Dominic" wrote:
> > >
> > > > You can create a new pivot table with suppliers in the row area and suppliers
> > > > in the data area, you will get a unique list of suppliers with the number of
> > > > instances each occurs in the data.
> > > >
> > > > Does this help?
> > > >
> > > > "j_cavanagh" wrote:
> > > >
> > > > > I have a PivotTable that contains a list of buyers, their suppliers, and part
> > > > > numbers. When I move the buyer and the supplier to the rows and put the
> > > > > suppliers also into the data area, I get a count of the parts for each
> > > > > supplier. I assume this is because the PivotTable is counting how many
> > > > > records contain that buyer,that supplier and the parts related to that
> > > > > supplier. Is there a way to only count the number of suppliers?
> > > > >
> > > > > Thank you.

A pivot table won't count unique values, but there's a workaround here,
if you're using an Excel list as the source:

http://www.contextures.com/xlPivot07.html#Unique


j_cavanagh wrote:
> I have a PivotTable that contains a list of buyers, their suppliers, and part
> numbers. When I move the buyer and the supplier to the rows and put the
> suppliers also into the data area, I get a count of the parts for each
> supplier. I assume this is because the PivotTable is counting how many
> records contain that buyer,that supplier and the parts related to that
> supplier. Is there a way to only count the number of suppliers?
>
> Thank you.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html