Forum Discussion

GJG's avatar
GJG
Copper Contributor
Dec 09, 2020

INDEX/MATCH using 2 criteria, and returning Nth value of INDEX/MATCH with 2 criteria

Hello,   I have a table in which people are assigned an amount of time to a task (in this case, teaching a course).  The first table is organized by person; their name repeat in column A, with cour...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 09, 2020

GJG 

For the collection Power Query solution

as

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"HOURS"}),
    #"Grouped Rows" = Table.Group(
        #"Removed Columns",
        {"COURSE"},
        {
            {"Tables",
            each 
                [RemoveCourse = Table.SelectColumns(_,{"ROLE", "PERSON"}),
                 SortRole = Table.Sort(RemoveCourse,{{"ROLE", Order.Descending}}),
                 TransposeTable = Table.Transpose(SortRole),
                 PromoteHeaders = Table.PromoteHeaders(TransposeTable, [PromoteAllScalars=true])
                ][PromoteHeaders]

            },
            {"Count", each List.Count(_)}
        }
    ),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Titles", each Table.ColumnNames([Tables])),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Count", Order.Descending}}),
    MaxRolesA = List.Max(#"Sorted Rows"[Count]),
    ColumnNames = List.Transform(
        {1..MaxRolesA},
        each
            if _ =1
            then "Coordinator"
            else "Assistance " & Text.From(_-1)
    ),
    Titles = #"Sorted Rows"[Titles]{0},
    RemoveUnused = Table.SelectColumns(#"Grouped Rows",{"COURSE", "Tables"}),
    ExpandTables = Table.ExpandTableColumn(RemoveUnused, "Tables", Titles, ColumnNames)
in
    ExpandTables

Resources