How to add repetitive names in Pivot table

[stalwart]

I have a raw data in which there are about 600 pharmacies, many of them have been repeated many times and so if we add all those it will show as 1000 (in pivot table) . Is there any way of counting repeated ones as one so that the final count will be 600. Appreciate your help in this.

[dilipandey]

Hi Stalwart,

Welcome to the forum.

I guess those 1000 names will appear as 600 only in pivot table as pivot displays only unique in rows and columns.
do you have some spaces at the beginning / end of the the pharmacies names ? thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[stalwart]

Hi dilip,
Thanks a lot for the prompt response.
you are right in the 1st column it is showing only the unique pharmacy names but in the 2nd column with COUNT OF PHARMACY NAME it is showing the count no as 1,1,1,1,4,20,1,1,1....of this sort and the total count as around 1000(ex.). So if a pharmacy no. is repeated 4 times in that list, it is showing the count as 4 and adding it in that list.....how to show the exact no. of pharmacies through pivot table...although it is clear that we can know the no as 600 by seeing on the left most digits...

[dilipandey]

yes.. you can refer to the left most column of pivot table where you have pharmacy name in rows.. and you can use a count / countif function somewhere in excel sheet. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[sarails]

If you are going to use the Pivot Table as the basis of outside formula, make sure that you learn the GETPIVOTDATA function as your data may not always appear in the same cell when you refresh your PIVOT.

Which will affect your formula results and this could cause yoru organisation to make decisions from innacurate data.