How do I change a table with 2 columns where I have repeating values in both columns into a matrix?

[RMF40]

Hopefully I will be able to explain my problem correctly.

I have a table with two columns in it. Column A has students in it, column B has the subject that each student is taking. Both column A and column B have repeating values, but together they are unique. For example

Name	Subject
Jimmy	Maths
Jimmy	Biology
Jimmy	Chemistry
Maxime	Maths
Maxime	Computer Science
Maxime	History

What I need to do is turn this into a matrix (is that the correct term?) which looks like this

Name	Subject A	Subject B	Subject C
Jimmy	Maths	Biology	Chemistry
Maxime	Maths	Computer Science	History

Can this be done?

[#2]

from my perspective, I guess a pivot table will do@RMF40 

[#3]

@RMF40 If you have Excel for Microsoft 365, you could try something along these lines (using the new PIVOTBY function):

=LET(
    a, FILTER(A2:B1000,A2:A1000<>""),
    r, CHOOSECOLS(a,1),
    v, CHOOSECOLS(a,2),
    n, ROWS(r),
    c, "Subject "&CHAR(MMULT((SEQUENCE(n)>=SEQUENCE(,n))*(r=TOROW(r)),EXPAND(1,n,,1))+64),
    PIVOTBY(r,c,v,CONCAT,0,0,,0)
)

See attached...

[#4]

@RMF40 

As variant that could be Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Students"]}[Content],
    GroupNames = Table.Group(
        Source, {"Name"},
        {
            {"Subject", each List.Sort( [Subject] )}
        }),
    SubjectNames = List.Transform(
        {1..List.Max( Table.TransformColumns(
            GroupNames,
            {"Subject", (q) => List.Count(q)} )[Subject] )},
            (e) => "Subject " & Character.FromNumber(e+64)
        ),
    ExtractSubjects = Table.TransformColumns(
        GroupNames,
        {"Subject", each Text.Combine(List.Transform(_, Text.From), ","), type text}
    ),
    SplitSubjects = Table.SplitColumn(
        ExtractSubjects,
        "Subject",
        Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), SubjectNames)
in
    SplitSubjects

Please check attached.

[#5]

@nduhiye2018 that was my initial thought, but the pivot table keeps putting totals into the cells rather than the subject choices. 

[#6]

@RMF40 

A Power Query variant attached:

let
    Source = TableSource,
    GroupedName = Table.Group(Source, {"Name"},
        {
            {"DATA", each
                Table.Transpose(
                    Table.SelectColumns(_, {"Subject"})
                ),
                type table
            },
            {"Count", Table.RowCount, Int64.Type}
        }
    ),
    RemovedCount = Table.RemoveColumns(GroupedName, {"Count"}),
    SortedName = Table.Sort(RemovedCount, {{"Name", Order.Ascending}}),
    ListMaxCount = { 1..List.Max( GroupedName[Count] ) },
    ExpandedSubject = Table.ExpandTableColumn(SortedName, "DATA",
        List.Transform( ListMaxCount, each "Column" & Text.From(_) ),
        List.Transform( ListMaxCount, each "Subject " & Character.FromNumber(_ +64) )
    )
in
    ExpandedSubject

[#7]

@RMF40 

Attached, with Power Query then Power Pivot

[#8]

@RMF40 

All you are trying to do is generated a list of subjects for each student.  Unfortunately the current implementation of Excel will simply return an error message "Nested arrays are not supported".  Not very helpful when the whole purpose of the calculation is to return a nested array"!  A possible solution is to use

=LET(
    student,      UNIQUE(Name),
    SubjectListλ, LAMBDA(n, TOROW(FILTER(Subject, Name = n))),
    subjectList,  MAPλ(student, SubjectListλ),
    SORT(HSTACK(student, subjectList))
)

The first line creates a list of distinct student names.  The second line defines a function that will return a list of subjects for any given student.  The third line uses MAP to return the subject list for each student in turn.  This is where the Excel in-built functionality fails.  What I have done instead is use a function MAPλ that I wrote to overcome the problem.  The final line sorts the data alphabetically by student name, to give

Note: The function can be found at

A version of Excel MAP helper function that will return an array of arrays (github.com)

It is complicated but the user need never look at it!