Duplicate groups in pivot table

Copper Contributor

I have a simple table with three groups, and I'm trying to tally how many males vs females.  I'm getting the following:

 

Count of Subject IDColumn Labels   
Row LabelsINRRZGrand Total
F26 8
F 1  1
M2216341
M 2 13
Grand Total2722453

I've done a find and replace on "M" and "F", but I'm still getting this separation.  Any idea why??

6 Replies

@janaki223 

Hi

Can you share a sample of data before pivoting in an excel file, to see the different columns in the source list and accordingly create the pivot table with 2 levels in the Row area then create Subtotals.

Thanks

Nabil Mourad

@janaki223 

There might be some additional spaces behind some F and M.

 

@Detlef Lewin 

Exactly, second F and M are with spaces at the end - "F " and "M "

image.png

@janaki223 

The assumption that there is a space may be correct for the "F" , however for the "M" there is a value in the 2 rows.

 I do not prefer to provide an answer without a sample data in Excel to be more accurate!

Thanks

Nabil Mourad

It did, in fact, turn out to be a trailing space causing the problem.  I thought I had done a correct global replacement, but even doing that, I ended up finding the individual rows causing the problems from the Pivot Table, and correcting the trailing space.

 

Thanks for the quick replies.

@nabilmourad 

 

The sample within text is copy/pasted from Excel file. If you copy/paste it back from post to Excel file you'll see extra spaces.