SOLVED

Excel Formula Problem

New Contributor

Hi, I have an excel sheet with thousands of rows four individual persons and hundreds of columns of categories. Each four consecutive columns can be clubbed as category. I want to do an analysis of this excel sheet and come up with an output as mentioned below in the sample.(Excel attached)

Haris1234_0-1623990635568.png

 

5 Replies

@Haris1234 

 

Not sure I would do this with thousands of rows & hundreds of columns (I would rather look at restructuring the sheet to enable this analysis and potentially other ones) but see attached file

best response confirmed by Haris1234 (New Contributor)
Solution

@Haris1234 

For best analysis change your structure from crosstabular form into a flat table:

Person - Category - Sub - Type - Value

 

Then it is just a matter of a quick pivot table.

 

@Haris1234 Since you mention "thousands of rows and hundreds of columns" I'd suggest you take a look at Power Query to flatten your data, as suggested by @Detlef Lewin . 

 

From there, you can create the desired output within Power Query, in Power Pivot (Data Model) or in a regular pivot table.

 

All of these are demonstrated in the attached file. 

@Haris1234 

Another Power Query + Power Pivot option (attached) in case you would need to analyse on others than A

Thank You All.
Flattening file and pivoting worked for me