Oct 11 2019 12:30 PM
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?
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 |
Oct 11 2019 01:04 PM
Oct 11 2019 01:12 PM
Buen día, @mpcassid
Oct 11 2019 02:57 PM
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
Oct 11 2019 07:41 PM
Thanks for the quick reply. Can you tell me how I could apply your query to my current workbook?@Detlef Lewin
Oct 11 2019 07:43 PM
Where would this code be inserted?@Sergei Baklan
Oct 12 2019 04:27 AM
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.
Oct 12 2019 10:33 AM
Thank you so much. You have been extremely helpful.@Sergei Baklan
Oct 12 2019 03:25 PM - edited Oct 12 2019 03:41 PM
The following is a solution using a modern dynamic array formula
= FILTER( Name, MMULT(N(Exams=@Subject), {1;1;1;1} ) )
Oct 13 2019 07:24 PM
@Peter Bartholomew ¡Realmente me encanta!