Unique Value Count with PivotTable Excel 2007

[Cachilupi]

I am using Excel 2007. I have a spreadsheet with two columns: Cost Center and Application Name. I would like to do a PivotTable that shows each cost center and the number of unique application names it has per cost center. For example, Cost Center N100 has 50 applications, but only 5 are unique such as Office 2007, Mathcad, Chemcad, MS Project, etc. Cost Center N200 has 100 applications with 10 unique apps (5 of which are the same as in cost center N100). What I would like to see is a PivotTable that says:

N100 5
N200 10

However, what I do see is a count of each individual application in each cost center, regardless of it being unique to the cost center.

 

[Fazza]

Hi,

Maybe like this. Please set it up in a new workbook - to avoid memory leak problems on creation - though after creating it can be moved into the original file. At the first step of the pivot table wizard take the external data option. Browse for your source data file, OK. If the source data file does not have a named range - not dynamic - for the source data then OK to the message about no visible tables and then hit 'options' and 'system tables' to see the worksheet names. I assume the data worksheet has headers in the first row and then data immediately under. Continue and take the option to edit in MS Query when available. Hit the 'SQL' button and edit the text you see to change the SQL to something like below. Untested, btw. Hit the SQL button again then the 'open door' icon to exit MS Query. Complete the pivot table.

HTH, Fazza

SELECT DISTINCT `Cost Center`, `Application Name`
FROM [Sheet1$]

 

[Cachilupi]

HI Fazza,

That actually worked great from what I can see. I didn't know you could do that with Excel data. Much thanks for helping me find a way to do it!!!

As a follow up question, I have to explain this process to a not so savvy Excel user. Is there anyway to possibly create a formula on a 3rd column of the source spreadsheet that does something similar (Count Distinct) and then create a pivot off of that rather than to get external data?

If not, I will do my best explaining the process to her. However, I am really impressed with the help and response! Thanks again!

 

[Fazza]

Hi,

What about one column to concatenate the two fields. So in C2, =A2&B2

Then in column D, D2 contains =1/countif(C:C,C2)

Then sum the D column field into the data area of the pivot table.

Cheers, F

 

[Cachilupi]

Fazza--Once again you amazed me! This works perfectly also. Thanks for your help, this way will be much easier to explain to my co-worker and will help in my future projects. Thanks again!

 

[Fazza]

 

[Claire_King]

Hello,

I have a similar problem in that I am trying to build reports that give results of unique occurences but the data I have needs to be reported based on three criteria.

I have a large spreadsheet (<10K rows) showing sales data for distributors. I am required to report on the sales per month for each distributor and how many different companies they have sold to. After searching around on the forum I tried the =1/COUNTIF(T:T,T2) solution which seemed to work but creates a problem when I have to create a pivot table / chart by individual distributors; it gives me occurences of 0.2 etc as the same reseller is present elsewhere in the data albeit assigned to a different distributor.

I have also tried using =IF(AND(D2=D1,T2=T1),0,1) as the reseller names are in column T and the dates are in column D but this still gives incorrect results as it gives me an occurence of 1 when there is a change in distributor (which is column A) as I have sorted by this column.

I feel as though I'm not making much sense but I am at a loss how to get the correct results!

Any help / advice would be great.

Thanks for taking the time to read.

 

[Fazza]

Hi, Claire.

Maybe start a new thread? It helps enormously to include some sample data inputs & outputs.

I don't know Excel 2007 - maybe its pivot tables already have some functionality for unique counts? Either way, be sure to mention the Excel version you're using.

regards, F