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

Question

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.&nbsp; I would like it to look similar to the table below.&nbsp; Can anyone recommend a formula that would complete this task?NameExam 1&nbsp;Exam 2Exam 3Exam 4Demitri&nbsp;SpanishMath&nbsp;&nbsp;LouisEnglishMusicMath&nbsp;AngelaMathSpanish&nbsp;&nbsp;DequanMusicPsychologyEnglishMathEmilyPsychologyEnglishMath&nbsp;Music&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SpanishMusicMath&nbsp;EnglishPsychologyDemitriLouisDemitriLouisDequanAngelaDequanLouisDequanEmily&nbsp;EmilyAngela&nbsp;&nbsp;&nbsp;&nbsp;Dequan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Emily&nbsp;&nbsp;

sergeibaklan · Answer

mpcassid&nbsp;
I repeated solution which&nbsp;Detlef_Lewin&nbsp;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.
&nbsp;
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.
&nbsp;
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-&gt;Name Manager give the name to the range, let say rngSignUp.
&nbsp;
Create new blank query like Data-&gt;Get Data-&gt;From Other Sources-&gt;Blank Query. Power Query editor will be opened, here find Home-&gt;Advanced Editor. Copy your variant of code from here below, or from attached file. Ctrl+A and paste this code into Advanced Editor window -&gt; Done. Be sure query works, after that Home-&gt;Close and Load To into Excel sheet.
&nbsp;
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] &lt;&gt; 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&nbsp;Detlef_Lewin&nbsp;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.

sergeibaklan · Answer

Detlef_Lewin&nbsp;
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
&nbsp;

peterbartholomew1 · Answer

mpcassid&nbsp;The following is a solution using a modern dynamic array formula= FILTER( Name, MMULT(N(Exams=@Subject), {1;1;1;1} ) )&nbsp;

srdobrais · Answer

PeterBartholomew1&nbsp;¡Realmente me encanta!

detlef_lewin · Answer

mpcassid&nbsp;It can be done with Power Query.See attached file.&nbsp;

Forum Discussion

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

9 Replies

Resources

Name	Exam 1	Exam 2	Exam 3	Exam 4
Demitri	Spanish	Math
Louis	English	Music	Math
Angela	Math	Spanish
Dequan	Music	Psychology	English	Math
Emily	Psychology	English	Math	Music

Spanish	Music	Math	English	Psychology
Demitri	Louis	Demitri	Louis	Dequan
Angela	Dequan	Louis	Dequan	Emily
	Emily	Angela
		Dequan
		Emily