Hi all,

Have a question regarding the Data Field in pivot tables. I have a number
of rows that have repeating values. What I want in the Data Field (Sum) is a
count of DISTINCT values in the column.

ie:
FR065817 \
FR065817 |---- counted as one
FR065817 /
FR066154 \___ counted as one
FR066154 /
FR066158 ----- counted as one
FR066159 \___ counted as one
FR066159 /

However I need all the data in the table as I'm using other columns for
different fields in the pivot table. Is there a way to do this? If I have
to split the table somehow, I'm willing to do it, but I'd need some help with
how do go about doing that.

Any help is appreciated.

Thanks,
Jay

Why not create 2 PivotTables? One that contains the distinct counts
and the other with the individual items? When you create the 2nd table
XL will ask you if it can base it on the first table. If you answer
yes, it will save some amount of resources.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi all,

Have a question regarding the Data Field in pivot tables. I have a number
of rows that have repeating values. What I want in the Data Field (Sum) is a
count of DISTINCT values in the column.

ie:
FR065817 \
FR065817 |---- counted as one
FR065817 /
FR066154 \___ counted as one
FR066154 /
FR066158 ----- counted as one
FR066159 \___ counted as one
FR066159 /

However I need all the data in the table as I'm using other columns for
different fields in the pivot table. Is there a way to do this? If I have
to split the table somehow, I'm willing to do it, but I'd need some help with
how do go about doing that.

Any help is appreciated.

Thanks,
Jay