'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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,026
Messages
5,835,022
Members
430,332
Latest member
Charly_Moon

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