SOLVED

# Pivot Table or Data Summarization and categorization of Column values

Highlighted
Occasional Contributor

# Pivot Table or Data Summarization and categorization of Column values

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

 Branch Annexure I Annexure II AHMED NAGAR No No AHMEDABAD Yes No

into following

 Type Yes No Annexure I Count Count Annexure II Count Count Annexure III Count Count Annexure IV to XI Count Count Annexure X Count Count Annexure XI Count Count Annexure XII Count Count

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.

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

# Re: Pivot Table or Data Summarization and categorization of Column values

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

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"``````

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

# Re: Pivot Table or Data Summarization and categorization of Column values

@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.