SOLVED

Pivot Table or Data Summarization and categorization of Column values

%3CLINGO-SUB%20id%3D%22lingo-sub-1652400%22%20slang%3D%22en-US%22%3EPivot%20Table%20or%20Data%20Summarization%20and%20categorization%20of%20Column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1652400%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%20I%20have%20attached%20workbook.%20I%20need%20to%20summarize%20the%20data%20which%20is%20like%20below%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22336%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EBranch%3C%2FTD%3E%3CTD%3EAnnexure%20I%3C%2FTD%3E%3CTD%3EAnnexure%20II%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22139%22%3EAHMED%20NAGAR%3C%2FTD%3E%3CTD%20width%3D%2294%22%3ENo%3C%2FTD%3E%3CTD%20width%3D%22103%22%3ENo%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22139%22%3EAHMEDABAD%3C%2FTD%3E%3CTD%20width%3D%2294%22%3EYes%3C%2FTD%3E%3CTD%20width%3D%22103%22%3ENo%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Einto%20following%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22385%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22133%22%3EType%3C%2FTD%3E%3CTD%20width%3D%22130%22%3EYes%3C%2FTD%3E%3CTD%20width%3D%22122%22%3ENo%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAnnexure%20I%3C%2FTD%3E%3CTD%3ECount%3C%2FTD%3E%3CTD%3ECount%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAnnexure%20II%3C%2FTD%3E%3CTD%3ECount%3C%2FTD%3E%3CTD%3ECount%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAnnexure%20III%3C%2FTD%3E%3CTD%3ECount%3C%2FTD%3E%3CTD%3ECount%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAnnexure%20IV%20to%20XI%3C%2FTD%3E%3CTD%3ECount%3C%2FTD%3E%3CTD%3ECount%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAnnexure%20X%3C%2FTD%3E%3CTD%3ECount%3C%2FTD%3E%3CTD%3ECount%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAnnexure%20XI%3C%2FTD%3E%3CTD%3ECount%3C%2FTD%3E%3CTD%3ECount%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAnnexure%20XII%3C%2FTD%3E%3CTD%3ECount%3C%2FTD%3E%3CTD%3ECount%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Ewithout%20formulae%3C%2FSTRONG%3E%20so%20that%20I%20can%20drill%20through%20the%20data%20when%20I%20click%20on%20count%20values%20to%20further%20investigate.%3C%2FP%3E%3CP%3EWhether%20it%20is%20possible%20in%20Pivot%20Table%20or%20Power%20Pivot%20or%20BI%20where%20I%20can%20have%20drill%20through%20or%20some%20other%20ways%20of%20data%20consolidation%20and%20not%20formula%20like%20sumif%20or%20countif.%20Advanced%20Filter%20will%20also%20be%20okay.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F34444%22%20target%3D%22_blank%22%3E%40Leila%20Gharani%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1652400%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1652542%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20or%20Data%20Summarization%20and%20categorization%20of%20Column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1652542%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F86737%22%20target%3D%22_blank%22%3E%40Ravitosh%20Kumar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20many%20way%20to%20handle%20such%20kind%20of%20data%2C%20below%20two%20are%20my%20favorite%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESolution%2001%20-%20Formula%20Solution%20Array%20CSE%3C%2FSTRONG%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7B%3DSUMPRODUCT((Table1%5B%5B%23Headers%5D%2C%5BAnnexure%20I%5D%3A%5BAnnexure%20XII%5D%5D%3D%24K2)*(Table1%5B%5BAnnexure%20I%5D%3A%5BAnnexure%20XII%5D%5D%3DL%241))%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EYou%20can%20use%20the%20above%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3Eyou%20need%20to%20copy%20and%20paste%20%26gt%3B%20transpose%20the%20column%20name%20in%20a%20row.%3C%2FLI%3E%3CLI%3EThe%20headers%20to%20have%20YES%20%26amp%3B%20No.%3C%2FLI%3E%3CLI%3EOnce%20your%20done%20with%20the%20formula%20press%20%3CSTRONG%3Ectrl%20%2B%20shift%20%2B%20enter%3C%2FSTRONG%3E%26nbsp%3B%3C%2FLI%3E%3C%2FOL%3E%3CP%3EIf%20you%20Microsoft%20365%20then%20you%20just%20need%20to%20press%20enter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_4bdd089b.png%22%20style%3D%22width%3A%20639px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F217505i6B1317119847A854%2Fimage-dimensions%2F639x143%3Fv%3D1.0%22%20width%3D%22639%22%20height%3D%22143%22%20role%3D%22button%22%20title%3D%22Snag_4bdd089b.png%22%20alt%3D%22Snag_4bdd089b.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESolution%2002%20-%20Power%20Query%20Solution%3C%2FSTRONG%3E%3C%2FP%3E%3COL%3E%3CLI%3EGrab%20your%20data%20into%20the%20Power%20Query%3C%2FLI%3E%3CLI%3EUn-pivot%20other%20columns%20selecting%20the%20Branch%20Name%3C%2FLI%3E%3CLI%3EGroup%20by%26nbsp%3BAnnexure%20%26amp%3B%26nbsp%3B%20YES%2C%20NO%3C%2FLI%3E%3CLI%3EPivot%20YES%2CNO%20with%20the%20count.%20make%20sure%20it%20is%20sum%3C%2FLI%3E%3C%2FOL%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Unpivoted%20Other%20Columns%22%20%3D%20Table.UnpivotOtherColumns(Source%2C%20%7B%22Branch%22%7D%2C%20%22Attribute%22%2C%20%22Value%22)%2C%0A%20%20%20%20%23%22Grouped%20Rows%22%20%3D%20Table.Group(%23%22Unpivoted%20Other%20Columns%22%2C%20%7B%22Attribute%22%2C%20%22Value%22%7D%2C%20%7B%7B%22Count%22%2C%20each%20Table.RowCount(_)%2C%20Int64.Type%7D%7D)%2C%0A%20%20%20%20%23%22Pivoted%20Column%22%20%3D%20Table.Pivot(%23%22Grouped%20Rows%22%2C%20List.Distinct(%23%22Grouped%20Rows%22%5BValue%5D)%2C%20%22Value%22%2C%20%22Count%22%2C%20List.Sum)%2C%0A%20%20%20%20%23%22Reordered%20Columns%22%20%3D%20Table.ReorderColumns(%23%22Pivoted%20Column%22%2C%7B%22Attribute%22%2C%20%22Yes%22%2C%20%22No%22%7D)%0Ain%0A%20%20%20%20%23%22Reordered%20Columns%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_4bdd62e0.png%22%20style%3D%22width%3A%20350px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F217506iC7B6E0A6D81F571E%2Fimage-dimensions%2F350x280%3Fv%3D1.0%22%20width%3D%22350%22%20height%3D%22280%22%20role%3D%22button%22%20title%3D%22Snag_4bdd62e0.png%22%20alt%3D%22Snag_4bdd62e0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi all, I have attached workbook. I need to summarize the data which is like below

 

BranchAnnexure IAnnexure II
AHMED NAGARNoNo
AHMEDABADYesNo

 

into following

 

TypeYesNo
Annexure ICountCount
Annexure IICountCount
Annexure IIICountCount
Annexure IV to XICountCount
Annexure XCountCount
Annexure XICountCount
Annexure XIICountCount

 

without formulae so that I can drill through the data when I click on count values to further investigate.

Whether it is possible in Pivot Table or Power Pivot or BI where I can have drill through or some other ways of data consolidation and not formula like sumif or countif. Advanced Filter will also be okay.

 

@Leila Gharani 

2 Replies
Highlighted
Best Response confirmed by Ravitosh Kumar (Occasional Contributor)
Solution

Hi @Ravitosh Kumar 

 

There are many way to handle such kind of data, below two are my favorite 

 

Solution 01 - Formula Solution Array CSE

{=SUMPRODUCT((Table1[[#Headers],[Annexure I]:[Annexure XII]]=$K2)*(Table1[[Annexure I]:[Annexure XII]]=L$1))}

You can use the above formula

 

  1. you need to copy and paste > transpose the column name in a row.
  2. The headers to have YES & No.
  3. Once your done with the formula press ctrl + shift + enter 

If you Microsoft 365 then you just need to press enter

 

Snag_4bdd089b.png

 

Solution 02 - Power Query Solution

  1. Grab your data into the Power Query
  2. Un-pivot other columns selecting the Branch Name
  3. Group by Annexure &  YES, NO
  4. Pivot YES,NO with the count. make sure it is sum
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Branch"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Attribute", "Value"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Value]), "Value", "Count", List.Sum),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Attribute", "Yes", "No"})
in
    #"Reordered Columns"

 

Snag_4bdd62e0.png

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

Highlighted

@Faraz Shaikh Thanks for the solution.

I found power query solution better and modified to use only till step 2 so that i can have drill through to see the branches.

Screenshot (11).png