I have a Microsoft Form that asks a person what airline they took and how much the ticket cost. They have the option to submit 3 arline tickets. The outcome ends up like the below table.
Employee | Airline | Cost | Airline2 | Cost2 | Airline3 | Cost3 |
|---|---|---|---|---|---|---|
Cody | Delta | 230 | American | 180 | ||
Kim | Southwest | 160 | American | 280 | Delta | 430 |
I am trying to merge the form answers into a 3 colums to make it look like the following using a formula:
Employee | Airline | Cost |
|---|---|---|
Cody | Delta | 230 |
Cody | American | 180 |
Kim | Southwest | 160 |
Kim | American | 280 |
Kim | Delta | 430 |
I have tried using INDEX, CONCAT, TOCOL, and I have yet to figure out a solution that will work. I know that I could make the form where the employee can only enter one arline per form but I am trying to make it where they only have to complete the form once per trip. Thank you for all the assistance!
