Merging multiple columns from one tab into a single column on another tab

Question

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!

Answer 1

you may try this

online tool

select * from basicUnstackToOneDim limit 3;cli_unstack~basicUnstackToOneDim~Airline,Cost;select * from basicUnstackToOneDim_unstack;

Employee	Airline1	Cost1	Airline2	Cost2	Airline3	Cost3
Cody	Delta	230	American	180
Kim	Southwest	160	American	280	Delta	430

Employee	Airline	Cost	上层属性
Cody	American	180	2
Cody	Delta	230	1
Kim	American	280	2
Kim	Delta	430	3
Kim	Southwest	160	1

Answer 2

Hi,

You may download my PBI file from here.

Hope this helps.