Transposing variable number columns to rows

[amw1]

I need to convert multiple excel files into a format that can be loaded into a database table. The files are answers to survey questions about software applications. The first question the user answers is what applications will they be rating. Then each question will have a column for the applications chosen that holds the rating. There is also a free form column that is used for each question. The number of applications can vary. For instance there could be 10 total and during the survey only 3 were chosen by the participants. Another survey might have 6 applications chosen.

For example there are 4 people taking the survey and their results look like:

Person ID	Office	Skype	Duo	Question1	Office	Skype	Duo	Question2	Office	Skype	Duo	Question3	Office	Skype	Duo	Question3
1	Office		Duo		2		2		1		2		1		4	everything works
2		Skype	Duo			3	4			3	2			N/A	3
3	Office	Skype	Duo		2	5	3	this is great	1	4	3		4	4	4
4	Office				4				3				4

I would like the data to be converted to:

ID	Name	Value	Question
1	Office	2
1	Duo	2
1	Office	1
1	Duo	2
1	Office	1	everything works
1	Duo	4	everything works
2	Skype	3
2	Duo	4
2	Skype	3
2	Duo	2
2	Skype	N/A
2	Duo	3
3	Office	2
3	Skype	5
3	Duo	3
3	Office	1	this is great
3	Skype	4	this is great
3	Duo	3	this is great
3	Office	4
3	Skype	4
3	Duo	4
4	Office	4
4	Office	3
4	Office	4

If I start with the transpose tool I can get part of the way but I am unsure of the best way to add a column for the free form entry for only the rows for that particular question.

I appreciate your help and suggestions and have attached a sample excel spreadsheet as well.

[JoBen · Accepted solution]

Hi @amw1, here is a workflow that should be able to display the data the way you need it. Here is the outcome that I came up with. 

[amw1]

Hi JoBen,

Thank you so much for the solution! I'm trying it out with actual data that has the variable applications but I think it should work fine.

Thanks again!

[JoBen]

Okay. You bet!