Home

Duplicate groups in pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-737001%22%20slang%3D%22en-US%22%3EDuplicate%20groups%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-737001%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20simple%20table%20with%20three%20groups%2C%20and%20I'm%20trying%20to%20tally%20how%20many%20males%20vs%20females.%20%26nbsp%3BI'm%20getting%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3ECount%20of%20Subject%20ID%3C%2FTD%3E%3CTD%3EColumn%20Labels%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERow%20Labels%3C%2FTD%3E%3CTD%3EINR%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3EZ%3C%2FTD%3E%3CTD%3EGrand%20Total%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EF%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EF%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EM%3C%2FTD%3E%3CTD%3E22%3C%2FTD%3E%3CTD%3E16%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E41%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EM%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EGrand%20Total%3C%2FTD%3E%3CTD%3E27%3C%2FTD%3E%3CTD%3E22%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E53%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EI've%20done%20a%20find%20and%20replace%20on%20%22M%22%20and%20%22F%22%2C%20but%20I'm%20still%20getting%20this%20separation.%20%26nbsp%3BAny%20idea%20why%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-737001%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-737045%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicate%20groups%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-737045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370732%22%20target%3D%22_blank%22%3E%40janaki223%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3ECan%20you%20share%20a%20sample%20of%20data%20before%20pivoting%20in%20an%20excel%20file%2C%20to%20see%20the%20different%20columns%20in%20the%20source%20list%20and%20accordingly%20create%20the%20pivot%20table%20with%202%20levels%20in%20the%20Row%20area%20then%20create%20Subtotals.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-737052%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicate%20groups%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-737052%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370732%22%20target%3D%22_blank%22%3E%40janaki223%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20might%20be%20some%20additional%20spaces%20behind%20some%20F%20and%20M.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-737083%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicate%20groups%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-737083%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExactly%2C%20second%20F%20and%20M%20are%20with%20spaces%20at%20the%20end%20-%20%22F%20%22%20and%20%22M%20%22%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20148px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122107i25DB9F15D7EBE070%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-737156%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicate%20groups%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-737156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370732%22%20target%3D%22_blank%22%3E%40janaki223%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20assumption%20that%20there%20is%20a%20space%20may%20be%20correct%20for%20the%20%22F%22%20%2C%20however%20for%20the%20%22M%22%20there%20is%20a%20value%20in%20the%202%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3BI%20do%20not%20prefer%20to%20provide%20an%20answer%20without%20a%20sample%20data%20in%20Excel%20to%20be%20more%20accurate!%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
janaki223
Occasional 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.

Related Conversations