Create list of values from multiple columns

Question

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:

1. Each name in the table is unique.
   
2. 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?

Answer 1

I'd opt for PowerQuery in this case.

• Step 1) - Select your table and load it into PowerQuery.
• Step 2) - Select the 'Flight' column and Unpivot the rest.
• Step 3) - Remove the column with Crewmembers.
• Step 4) - Sort the 'Value' column ascending and filter out the empty rows.

It really is just a few clicks, but here below is the M-code to get you going:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Flight", type text}, {"Crew Member 1", type text}, {"Crew Member 2", type text}, {"Crew Member 3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Flight"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Value] <> null and [Value] <> ""),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Value", Order.Ascending}})
in
    #"Sorted Rows"

Load the result back into Excel:

If you want, you can change headers and all that.

Answer 2

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: