Pivot Table - Count unique values (clients) within each sector, EXL10

[marte777]

Hi,


I am having trouble counting unique number of clients within each sector in my Pivot table.
I have Excel 2010, and if possible I would like to avoid VBA.


I have created an illustrative example of my issue below:

Client	Industry
McDonalds	Restaurants
Burger King	Restaurants
McDonalds	Restaurants
BMW	Auto
EasyJet	Airline
Ryanair	Airline
EasyJet	Airline

<tbody>
</tbody>

Desired result:

Industry	Unique count
Restaurant	2
Airline	2
Auto	1

<tbody>
</tbody>

Any help would be highly appreciated!

 

[oldbrewer]

Count of Industry

Industry

Client

Airline

Auto

Restaurants

Grand Total

BMW

1

1

Burger King

1

1

EasyJet

2

2

McDonalds

2

2

Ryanair

1

1

Grand Total

3

1

3

7

Industry

Unique count

Restaurant

2

Airline

2

Auto

1

the trick now is make a formatted copy of the pivot table

counting the cells that have any number in them and subtracting 1

Client

Industry

McDonalds

Restaurants

Burger King

Restaurants

McDonalds

Restaurants

Airline

Auto

Restaurants

BMW

Auto

2

1

2

EasyJet

Airline

Ryanair

Airline

EasyJet

Airline

this lower table automatically expands to match the number of columns in the pivot table

formula giving auto

=IF(OR(H5="",H5="Grand Total"),"",H5)

formula giving the 2 under airline

=IF(G21="","",COUNTA(G6:G13)-1)

if it is vital to have industries going down rather than across this can be done…….

<colgroup><col><col><col span="3"><col><col span="3"><col><col span="4"></colgroup><tbody>
</tbody>

 

[marte777]

"it is vital to have industries going down rather than across this can be done……."

How do I put all the industries horizontally, rather than vertically using the pivot field list?

 

[oldbrewer]

Client

Industry

McDonalds

Restaurants

Burger King

Restaurants

Count of Industry

Client

McDonalds

Restaurants

Industry

BMW

Burger King

EasyJet

McDonalds

Ryanair

Grand Total

BMW

Auto

Airline

2

1

3

EasyJet

Airline

Auto

1

1

Ryanair

Airline

Restaurants

1

2

3

EasyJet

Airline

Grand Total

1

1

2

2

1

7

Airline

1

1

Auto

1

Restaurants

1

1

Airline

2

Auto

1

Restaurants

2

just put industry in left position of pivot

then achange any number other than 1 to a 1

then sum the 1's in the rows

<colgroup><col><col><col span="2"><col><col span="5"><col><col span="2"></colgroup><tbody>
</tbody>