How can I search a range of text and return value of text from first column?

Copper Contributor

I want to search a range of text (exams students have signed up for) and create a list of students who have signed up for each exam.  I would like it to look similar to the table below.  Can anyone recommend a formula that would complete this task?

NameExam 1 Exam 2Exam 3Exam 4
Demitri SpanishMath  
LouisEnglishMusicMath 
AngelaMathSpanish  
DequanMusicPsychologyEnglishMath
EmilyPsychologyEnglishMath Music
     
SpanishMusicMath EnglishPsychology
DemitriLouisDemitriLouisDequan
AngelaDequanLouisDequanEmily
 EmilyAngela  
  Dequan   
  Emily  
9 Replies

@mpcassid 

It can be done with Power Query.

See attached file.

 

@Detlef Lewin 

With adding a bit of code your solution could be shortened and be independent on number of names

let
    Source = Tabelle1,
    UnpivotOtherColumns = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    GroupRows = Table.Group(UnpivotOtherColumns, {"Value"}, {{"Names", each [Name]}}),
    Result = Table.FromRows(List.Zip(GroupRows[Names]),GroupRows[Value])
in
    Result

 

Thanks for the quick reply.  Can you tell me how I could apply your query to my current workbook?@Detlef Lewin 

@mpcassid 

I repeated solution which @Detlef Lewin suggested, only modified the code a bit.

Perhaps you are not familiar with Power Query, when from scratch.

If you are on Excel for Mac that solution doesn't work. If on Excel 2016 or later, Power Query is built into Excel. If earlier version (2010, 2013) you need to download from Microsoft Downloads and install free Power Query add-in.

 

With your actual file - you have at least two options. First, which is preferable convert your source data into Excel Table. Stay within the range (or select it), Ctrl+T, check your table has headers, Enter. Rename the table as suitable, in my sample tblSignUp.

 

Second option use your data as named range, Select it, better with some gap on future expansion, with one-two empty columns to the right and with empty rows down. In Formulas->Name Manager give the name to the range, let say rngSignUp.

 

Create new blank query like Data->Get Data->From Other Sources->Blank Query. Power Query editor will be opened, here find Home->Advanced Editor. Copy your variant of code from here below, or from attached file. Ctrl+A and paste this code into Advanced Editor window -> Done. Be sure query works, after that Home->Close and Load To into Excel sheet.

 

Above is one variant of steps to perform, that could other ways, just keep an idea.

Code to the table:

let
    Source = Excel.CurrentWorkbook(){[Name="tblSignUp"]}[Content],
    UnpivotOtherColumns = Table.UnpivotOtherColumns(
        Source,
        {"Name"}, "Attribute", "Value"
    ),
    GroupRows = Table.Group(
        UnpivotOtherColumns,
        {"Value"}, {{"Names", each [Name]}}
    ),
    Result = Table.FromRows(
        List.Zip(GroupRows[Names]),GroupRows[Value]
    )
in
    Result

Code for the range:

let
    Source = Excel.CurrentWorkbook(),
    FilteredOnRange = Table.SelectRows(Source, each ([Name] = "rngSignUp")),
    KeepTable = Table.SelectColumns(FilteredOnRange,{"Content"}),
    ColumnNames = Table.ColumnNames(KeepTable[Content]{0}),
    ExpandRange = Table.ExpandTableColumn(
        KeepTable,
        "Content",
        ColumnNames,
        ColumnNames
    ),
    ExamsToHeaders = Table.PromoteHeaders(
        ExpandRange,
        [PromoteAllScalars=true]
    ),
    NewColumnNames = Table.ColumnNames(ExamsToHeaders),
    ColumnsToKeep = List.RemoveMatchingItems(
        NewColumnNames,ColumnNames),
    KeepExamsColumns = Table.SelectColumns(
        ExamsToHeaders,
        ColumnsToKeep
    ),
    RemoveEmptyRows = Table.SelectRows(
        KeepExamsColumns,
        each ([Name] <> null)
    ),
    UnpivotOtherColumns = Table.UnpivotOtherColumns(
        RemoveEmptyRows,
        {"Name"}, "Attribute", "Value"
    ),
    GroupRows = Table.Group(
        UnpivotOtherColumns,
        {"Value"}, {{"Names", each [Name]}}
    ),
    Result = Table.FromRows(
        List.Zip(GroupRows[Names]),GroupRows[Value]
    )
in
    Result

Alternatively you may not care about the code, just take @Detlef Lewin variant and repeat all steps from his query on your actual data using Power Query user interface only. Here you shall be careful with number and names of the columns.

Thank you so much.  You have been extremely helpful.@Sergei Baklan 

@mpcassid 

The following is a solution using a modern dynamic array formula

= FILTER( Name, MMULT(N(Exams=@Subject), {1;1;1;1} ) )