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.
