How to Return Column Data Based on All Populated Cells in Row Range and Match to Other Worksheet

Hi Anthony,

That could be done by Power Query. Here is the simple mockup to illustrate idea based on your initial pictures, for the real data queries will be bit more complex.

Here on the left are source tables and resulting one are on the right

First query generates list of roles for users, here it dynamically split on columns

let

    Source = Excel.CurrentWorkbook(){[Name="User"]}[Content],
    UnpivotJobs = Table.UnpivotOtherColumns(Source, {"User ID"}, "Attribute", "Value"),
    RemoveValues = Table.RemoveColumns(UnpivotJobs,{"Value"}),
    GroupRows = Table.Group(RemoveValues, {"User ID"}, {{"Role", each [Attribute]}}),
    MaxInList = Table.AddColumn(GroupRows, "ListMax", each List.Count([Role])),
    ElementNumber = List.Max(MaxInList[ListMax]),
    ListColumns = List.Transform({1..ElementNumber}, each "Role " & Number.ToText(_)),
    ExtractElements = Table.TransformColumns(GroupRows,
        {"Role", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    SplitElements = Table.SplitColumn(ExtractElements, "Role",
        Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), ListColumns)

in
    SplitElements

For the second table we first create connection query which maps roles on lists of classes

let
    Source = Excel.CurrentWorkbook(){[Name="Job"]}[Content],
    UnpivotClasses = Table.UnpivotOtherColumns(Source, {"Job"}, "Attribute", "Value"),
    RemoveValue = Table.RemoveColumns(UnpivotClasses,{"Value"}),
    GroupRows = Table.Group(RemoveValue, {"Job"}, {{"Class", each [Attribute]}})
in
    GroupRows

and here we take users with roles and merge with that query

let

    Source = Excel.CurrentWorkbook(){[Name="User"]}[Content],
    UnpivotJobs = Table.UnpivotOtherColumns(Source, {"User ID"}, "Job", "Value"),
    RemoveValues = Table.RemoveColumns(UnpivotJobs,{"Value"}),
    MergeWithClasses = Table.NestedJoin(RemoveValues,{"Job"},ClassesForRoles,
	{"Job"},"Classes",JoinKind.LeftOuter),
    ExpandClasses = Table.ExpandTableColumn(MergeWithClasses, "Classes", {"Class"}, {"Class"}),
    ExtractClasses = Table.TransformColumns(ExpandClasses, {"Class",
	each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    SortUsers = Table.Sort(ExtractClasses,{{"User ID", Order.Descending},
	{"Job", Order.Ascending}})
in
    SortUsers

Here classes are as lists, could be split dynamically same way as roles in first query. Sample file is attached.