Create list of values from multiple columns-CodePudding

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?

CodePudding user response：

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.