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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies