'Create list of values from multiple columns

Data-Base

   |      A    |          B         |        C         |         D        |
---|-----------|--------------------|------------------|------------------|---
1  |   Flight  |    Crew Member 1   |  Crew Member 2   |   Crew Member 3  |
2  |    F001   |       Michael      |     Katrin       |       Karl       |
3  |    F002   |       Jennifer     |     Peter        |       Karl       |
4  |    F003   |       Peter        |                  |                  |
5  |    F004   |       Karl         |     Michael      |                  |
6  |    F005   |       Jennifer     |     Michael      |       Katrin     |
7  |    F006   |       Peter        |     Karl         |       Michael    |
8  |    F007   |       Karl         |     Jennifer     |                  |

Note:

Each name in the table is unique.
Each name can only appear one time per row.

Expected Result

   |      E        |          F         |      
---|---------------|--------------------|--
1  |      Name     |       Flights      |  
2  |    Jennifer   |       F002         |
3  |    Jennifer   |       F005         |
4  |    Jennifer   |       F007         | 
5  |    Katrin     |       F001         |
6  |    Katrin     |       F005         |
7  |    Karl       |       F001         |
8  |    Karl       |       F002         | 
9  |    Karl       |       F004         | 
10 |    Karl       |       F006         | 
11 |    Karl       |       F007         | 
12 |    Michael    |       F001         | 
13 |    Michael    |       F004         | 
14 |    Michael    |       F005         | 
15 |    Michael    |       F006         | 
16 |    Peter      |       F002         | 
17 |    Peter      |       F003         | 
18 |    Peter      |       F006         |

As you can see in the data-base there is a list with mulitple flights.
In Columns B:D the crew members for each flight are displayed.

Now I want to list all the flights for each crew member.

Therefore, I am wondering what formular I need to

a) List all the crew members in Column E based on how many times they appear in Column B:D
b) Assign each flight to them in Column F

Do you have any idea how to solve this issue?

Solution 1:^[1]

Let me start of with Power Query being the best option here (as posted by JvdV). But I wanted to see if I could make it work with a formula in Office 365:

=LET(data,A2:D8,
          data1,INDEX(data,,1),
                     cdata,COLUMNS(data)-COLUMNS(data1),
          data2,INDEX(data,SEQUENCE(ROWS(data)),SEQUENCE(1,cdata,1+COLUMNS(data1))),
                     rdata2,ROWS(data2),
                     cdata2,COLUMNS(data2),
                     seq,SEQUENCE(rdata2*cdata2),
                     mseq,MOD(seq-1,rdata2)+1,
                                x,INDEX(data2,mseq,SEQUENCE(1,cdata2)),
          unpiv,SUBSTITUTE(INDEX(x,mseq,ROUNDUP(seq/rdata2,)),"",""),
          names,INDEX(data1,mseq),
                               a,CHOOSE({1,2},unpiv,names),
                               b,--(INDEX(a,,1)<>""),
SORT(FILTER(a,b=1),{1,2}))

This results in the unpivoted flights/names (without headers) sorted by flight/names alphabetically:

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	P.b

'Create list of values from multiple columns

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]