'Flatten' values in table to individual lines for Pivot Table purposes

excelhippo

New Member
Joined
Dec 9, 2016
Messages
1
Hi there,

This is my first post here - hoping that someone can shed some light on this for me!

I have a table that has a feedback question, question rating, and number of respondents that provided that rating - see below:

Feedback QuestionRatingNumber of Respondents
Did the course meet your expectations?43
Would you recommend this course to others?52

<tbody>
</tbody>

What I want to do is automatically ungroup the number of respondents, so that there are 3 lines of a rating of 4 for the first question, and 2 lines of a rating of 5, so that I can work with this data in a pivot table easily. Example below:

Feedback QuestionRating
Did the course meet your expectations?4
Did the course meet your expectations?4
Did the course meet your expectations?4
Would you recommend this course to others?5
Would you recommend this course to others?5

<tbody>
</tbody>


I'm familiar with ALT+D+P using multiple consolidation ranges to break out data from a crosstab table, but it doesn't seem to help the issue that I'm having here. I ended up having to manually insert lines to create a dataset that was right for a pivot table and I would rather not do it again if possible. I have searched online but I don't think I'm using the right terminology since everything I google brings me back to ALT+D+P and multiple consolidation ranges.

Any advice on how to automate this process would be greatly appreciated!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Watch MrExcel Video

Forum statistics

Threads
1,122,912
Messages
5,598,828
Members
414,260
Latest member
joishe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top