How can I turn part of the Excel data to columns to get a desired output?

user_9013283 0
Viewed 46

For eg - Say I have data in the following format -

Current Format
enter image description here

I would need the data to be formatted in the following format for ease of use -

Required Format
enter image description here

Of course the data contains a lot more records - I'm looking for an easy way to transpose data in this way for large sets of data.

Any help will be appreciated :)

data-cleaning transpose rows excel
2 Answers

What you want to do is called:

Unpivot:

  1. Select the data and on the Insert menu choose Table
  2. On the Data menu click From Table/Range
  3. The query window will open. Choose the columns you need to extract. With your data the columns to highlight are "Type" and "Number of Cases"
  4. On the Tranform menu choose Unpivot Columns
  5. If the data looks right now, close the Query Editor (accepting changes).

Examples here: Unpivot Excel Data

img

user_81127760

This is very easy with PowerQuery. It is inbuilt for Excel 2016 and a freely available add in for Version from 2010 to 2013.

You would set your data up as a table excluding the first row which contains the text Number of Cases (Ctrl + T whilst bring up window to create table)

Create table

Then from the data tab (Excel 2016) or the Powerquery tab (earlier versions) select data from table and use your newly created table as source.

Highlight the last 3 columns and right click > unpivot columns

Unpivot

Double click in headers section and rename Attribute as Type and Value as Number of Cases

Rename headers

Top left select close and load to

Close and load to

Select table and load to new worksheet (or existing)

Load to

View result

enter image description here

user_62412350
Related