excel - 如何在Excel中的列中应用分组值

Question

我在excel中有这样的数据，

class            Subject
A                  maths
A                  science
A                  english
B                  maths
B                  science

excel中的预期输出是:

Class             Subject1         subject2          subject3
A                 maths             science          english
B                 maths             science

我在excel中尝试了组选项，但没有奏效。
请让我知道如何获得预期的输出

Answer 1

由于这是 stackoverflow，一个编程问答网站，我认为 vba 解决方案可能适用:

BE CAREFUL, this code will change your date layout, make a copy before running it!

Public Sub GroupByExpandingColumn()
    Dim ws As Worksheet
    Dim row, col As Long

    'Assuming range for your data is A1:B6

    ' Change this to reflect your Sheet if needed (i.e by name like Set ws = Workbooks("sheet name"))
    Set ws = ActiveWorkbook.ActiveSheet

    'First of all, make sure the data is ordered according to the group by column, in this case CLASS - A column
    ' This can probably be achieved in a simpler way, I've just hacked this code from a recorded macro
    ws.Range("A1:B1").Select
    ws.Range(Selection, Selection.End(xlDown)).Select
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A2:A6"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:B6")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    'Now that the data is ordered, let's do our magic
    row = 3
    While Len(ws.Cells(row, 1).Value) > 0
        If ws.Cells(row, 1).Value = ws.Cells(row - 1, 1).Value Then
            'same class than previous row, group in a new column
            col = 3
            While Len(ws.Cells(row - 1, col).Value) > 0
                col = col + 1
            Wend
            ws.Cells(row - 1, col).Value = ws.Cells(row, 2).Value
            ws.Range(row & ":" & row).Delete
        Else
            'Next class
            row = row + 1
        End If
    Wend

    Set ws = Nothing
End Sub