CODEWITHSUNDEEP
excel
mathcounterexamples.net
mathcounterexamples.net

Reputation: 491

Avoiding duplicate rows in an EXCEL Pivot Table

I am running EXCEL 2007 and try to do a Pivot table.

The data are the following ones: enter image description here

And the table pivot result: enter image description here

As you can see, I have some duplicate lines in the pivot table that I don't want.

What can be the issue? Column B are all numbers. And column C all texts.

Thanks.

Upvotes: 0

Views: 2384

Answers (1)

Rusan Kax
Rusan Kax

Reputation: 1894

This issue is likely to be some of your labels "Pro1" may have white space at the end. i.e. "Pro1" and "Pro1 " would be interpreted as different labels.

You can get rid by using a trim function TRIM() applied to your row labels.

To do so, in another column put the formula TRIM(C2), and copy down until the bottom of your Pro column is reached. Then copy and paste by value this data over your Pro column. This will replace the entries "Pro1 " (with space at end) with "Pro1" (no space at end). You can then get rid of the extra column you created.

Once this is done, to reflect the changes, the pivot table will need to be refreshed.

There are other ways to do this, but this is the most direct and straight forward.

Upvotes: 1

Related Questions