Transpose Issue

[Jo-B]

Dear all, 

I did today a lot of trail and error on the following issue.

I have a certain data set with many columns (Question Scores and Comments to each Questions). 

ID	Q1	Q1-Comments	Q2	Q2 Comments	Q3	...
1	10	def	9	abc	2
2	9	xyz	10	abc	6

I am looking for the following layout to filter on on the different questions and comments.

ID	Question	Score	Comments
1	1	10	def
1	2	9	abc
1	3	...	..

I know to transpose the column related to the scores of the questions, however how I can assign the right comment to the correct row?

I appreciate your help!

Cheers, 

Johanna

[jdunkerley79 · Accepted solution]

You need to do a little post processing after the transpose something like:

This uses Regex to get the question number then a crosstab to put back together

Sample attached.

[mrichards · Accepted solution]

You could also try it with a multi-row formula followed by a filter (workflow attached).

[Jo-B]

Dear  Both,

I could work with solution 2, although #1 is also quite Logical, however with my data i was a bit confused....

Thanks to you both!!

Best Regards,

Johanna