Excel 2007 : Pivot Table unique values

I have a very large data source from which I create Pivot Tables.
When I ask for a Count (of names), the Pivot Table does not count Unique values, but counts each individual entry. How can I get it to summarize the unique values?

Can you give an example of what you mean?

Are there any slight differences? Like extra spaces, etc...?

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Can you not alter the SQL export to provide distinct data? If not, you will need an additional formula column in the source data for the pivot.

As pointed out by Romperstomper, see attached with some helper columns added...

the first 2 columns concatenate the Division and respective Insured/Policy columns (this is the next 2 columns can use simple formulas)..

Then next 2 columns use countifs to decide the unique records.. with formulas:

=IF(COUNTIF(M:M,M2)=1,1,0)

and

=IF(COUNTIF(N:N,N2)=1,1,0)

Then the Pivot Table Sums these columns...

You need an additional formula like in the attached. There is no way to do it inside the table.

Hi penny.rhine

I think you need to create another table where you have deleted duplicates in the fileds you are counting. In 2003 Excel there is not a "delete dupilicate" function built in. In the newer version you can delete duplicate records from your table based on selected fields (column names) of your data. I believe you need the 2003 Add-In or a newer version to accomplish your task.

or

Above you said you have the SQL data on your own. Perhaps you can do a SQL "Select Distinct" command to get the recordset you need before you do Excel on it? See http://www.w3schools.com/sql/sql_distinct.asp

or

Maybe some of the smart gurus have a method.
I see I'm a little late on my answer.

I am not so smart though...

My formulas don't work as they are... they should be:

=IF(COUNTIF(M$2:M2,M2)=1,1,0)

and

=IF(COUNTIF(N$2:N2,N2)=1,1,0)

copied down.

Hi penny.rhine, you have marked this thread solved and you gave me rep comments (thanks) indicating that you found another way around the problem due to the fact you have over 60,000 records (and I guess our formulas were lagging).

If you could kindly supply your solution, it would be appreciated as it will help any future users with same issue to get the most effective answer.

Thank you.