How to prevent some data from being duplicated in a column?

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

How to prevent some data from being duplicated in a column?

Post by BittenApple »

Hello team,

I have data as this:

Plan Measure num1 num2
AA diagcode1 1000 44
AA diagcodeb 1000 55

I want to insert a pivot table for the data on the top, but I want 1000 for num1 to be only only one time, not two times.
Is there a way to take care of duplications in a pivot table in num1?

Regards,
verygreen

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to prevent some data from being duplicated in a column?

Post by HansV »

What does your pivot table look like?
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: How to prevent some data from being duplicated in a column?

Post by BittenApple »

Hello Hans,
Please find the attachment for your review.
Respectfully,
Bita
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to prevent some data from being duplicated in a column?

Post by HansV »

Do you mean that you want to see 150 for ABC, and 2143 for DD?
If so, click on 'Sum of Num1' in the Values area of the PivotTable Fields pane.
Select 'Value Field Settings...' from the dropdown menu.

S0463.png

Select Max (or Min or Average) from the 'Summarize value field by' list box.
Then click OK.

S0464.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: How to prevent some data from being duplicated in a column?

Post by BittenApple »

Hello Hans,
Thanks for your response.
I want to see 150 one time and 2143 one time regardless of ABC and DD.
Thanks,
Bita

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to prevent some data from being duplicated in a column?

Post by HansV »

Remove Measure from the pivot table, and set the summarize function for Num1 to Max.
You may want to turn of the Grand Total too.

S0467.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: How to prevent some data from being duplicated in a column?

Post by BittenApple »

Thanks for the response.