pulling data into a new format so I can create a 100% stacked bar chart

I am going nuts trying to figure this out and would love some help!

I am trying to create a new table that is formatted correctly for me to create a 100% stacked bar chart.

The existing table is in A2:S22.

I need to pull this data into the table on the right to populate all the cells in yellow highlight.

Very grateful for any assistance!

Thanks very much, that is working but is there a way to make this update when I drag down through the whole sheet?
I will have more than Table 1 on the left side from top to bottom so I was hoping for the tale to update.
I can't simply update the relative references as that messes it up.

thanks both, I've updated the attached spreadsheet to try and illustrate what I want to do.

With 'data1' as a dummy subset of the format of full data.

I am trying to analyse multiple questions with slight changes to the sample. I use countifs to get the data first
then I need to reformat it in the style in table on right to produce the correct chart...

Originally Posted by JeteMc

Try the following in cell X2:

Formula:

Please Login or Register  to view this content.

Once the formula is entered into cell X2 drag the fill handle over to cell AB2 and then, while cells X2:AB2 are still selected, double click the fill handle to copy the formula down.
Let us know if you have any questions.

Thanks so much for your reply. Apologies I didn’t say this sooner I had to crack on with analysis and got side tracked , it’s now done!