Excel transpose and merge

New Contributor

Does anyone know a function or method in excel of getting data in a state from Illustration 1 to Illustration 2. Transform could be part of the solution but I need a way to consolidate rows and columns at the same time

snip.jpg

6 Replies

@clieboff 

Power Query could help

cool. I've never used before. do you happen to know how that could work and the steps?

@clieboff 

Sure, if only you share sample file instead of screenshot

i updated my post to include. much appreciated.

@clieboff How about a pivot table? See attached.

@clieboff 

Thank you.

- Not exactly necessary, but it's better to transform source data into structures table (Ctrl+L or Cltrl+T staying within it)

- Right click on it and Get Data from Table/Range or Get Data from Sheet or like depends on your Excel version

- in Power Query editor select Service Level 2 column, Transform->Pivot Column, Value as value

- land result into Excel sheet

- adjust formats as needed

 

Alternatively you may create PivotTable based o  your source, also in attached.