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
How to prevent some data from being duplicated in a column?
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
-
- Administrator
- Posts: 78485
- 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?
What does your pivot table look like?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: How to prevent some data from being duplicated in a column?
Hello Hans,
Please find the attachment for your review.
Respectfully,
Bita
Please find the attachment for your review.
Respectfully,
Bita
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78485
- 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?
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.
Select Max (or Min or Average) from the 'Summarize value field by' list box.
Then click OK.
If so, click on 'Sum of Num1' in the Values area of the PivotTable Fields pane.
Select 'Value Field Settings...' from the dropdown menu.
Select Max (or Min or Average) from the 'Summarize value field by' list box.
Then click OK.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: How to prevent some data from being duplicated in a column?
Hello Hans,
Thanks for your response.
I want to see 150 one time and 2143 one time regardless of ABC and DD.
Thanks,
Bita
Thanks for your response.
I want to see 150 one time and 2143 one time regardless of ABC and DD.
Thanks,
Bita
-
- Administrator
- Posts: 78485
- 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?
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.
You may want to turn of the Grand Total too.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: How to prevent some data from being duplicated in a column?
Thanks for the response.