Pivot Table question

[Rum]

Hi,

I have a excel data set with 7000 rows and about 10 columns.

Example: Columns are:

‘Login Id’; ‘Int Vs Ext’; ‘TA’; ‘Protocol’; ‘Site #’

Under all these heading of columns are multiple records:

Example: If there are 20 unique login ids each appears multiple times. So is
the case with Int Vs Ext, TA, Protocol, Site #

I am trying to find:
1. Average number of ‘protocols’ per ‘login id’
2. Average no of ‘sites #’ per ‘login id’

I have been trying to do this using a pivot table and I get a count of all
the protocols that correspond to a Login Id. This is a reflection of how many
times a protocol appears in the data set. But I multiple entries of the same
protocol to be counted as one. This way if a login Id had for example 100
protocol entries, I wish to get how many of those 100 protocol entries are
unique. Example: if XXX appears 20 times, YYY appears 30 times, ZZZ appears
50 times in protocol records I need it to be counted XXX, YYY, ZZZ each once
making it a unique count of 3.

I would really appreciate your insights on this matter.

Rum

 

[Daryl S]

Rum -

If you really need to do this with pivot tables (instead of database
features, or using filters and counts on the login id field), then there is a
way, though not very elegant. If you have 12 products, and you want to know
the number of login ids with those products, then you will end up with 12
'secondary' pivot tables - one for each product. If this is OK, then
proceed as follows:

Create your original pivot table with counts of products by login ids. The
login ids will be the rows, and the product ids will be the column heads.
This is just the basis for the 'secondary' pivot tables.

Now for the first 'secondary' pivot table, say for product XXX, do the
following:
Invoke the pivot table wizard and select the 'Microsoft Excel list or
database' option, and click Next.

Choose the data section of the original pivot table, including the row with
the product ids, but not the row above with 'Count of Products' in it, and
don't include any total columns to the right or total rows below the data.
Click Next.

Choose the location for this 'secondary' pivot table and click Layout...

Drag the Login ID to the Rows section, and the first product (e.g. XXX) to
the Data section. If the data section says 'Sum of XXX' instead of 'Count of
XXX', then double-click on it and change it to Count. Click OK, then click
Finish. The resulting pivot table will show you all logins, and will have a
1 for those that had any records with product XXX, and be blank for those
logins that do not have any records for product XXX.

Do this for each product. The only difference for the remaining products is
that you would choose an existing Pivot Table Report (choose the first
'secondary' pivot table for this), instead of a Microsoft Office Excel
worksheet. If you forget this, Excel is pretty good about asking you for it.

Hope that is what you are looking for!