☰

在 Excel 中按特定列列出以进行邮件合并

tag-icon • tag-icon microsoft-excel microsoft-word
在 Excel 中按特定列列出以进行邮件合并

我想列出所有相同的学生及其课程。

我的例子

School        Teacher   Course      ID      LastName
705 KELLEY    AP        PSYCHOLOGY  1245    AARON             NICHOLAS
705 KELLEY    AP        PSYCHOLOGY  2314    ALARCON-ESPINOZA  LOURDES
705 GAMARRA   AP        SPANISH V   4561    ALCORCHA          JOSE
705 KELLEY    AP        PSYCHOLOGY  5555    CARDOZA           JOSUE
705 GAMARRA   AP        SPANISH V   5555    CARDOZA           JOSUE
705 GAMARRA   AP        SPANISH V   5641    CARDOZA           MARISOL

应该看起来像这样

School     ID      LastName   FirstName    course 1        course 2
705        5555    CARDOZA    JOSUE        AP Psychology   AP Spanish V

答案1

以下是一些 VBA，应该可以让您相当接近。假设此数据在 Sheet1 中，标题在第 1 行，课程列为“C”：

Sub crosstabCourses()
Dim prevStudent, currStudent As String
Dim rowWrite, colWrite, rowRead As Integer

rowWrite = 0
colWrite = 5

For rowRead = 2 To 7
    currStudent = Sheet1.Cells(rowRead, 4)
    If currStudent <> prevStudent Then
        rowWrite = rowWrite + 1
        colWrite = 5
    Else
        colWrite = colWrite + 1
    End If

    Sheet2.Cells(rowWrite, 1) = Sheet1.Cells(rowRead, 1).Value
    Sheet2.Cells(rowWrite, 2) = Sheet1.Cells(rowRead, 4).Value
    Sheet2.Cells(rowWrite, 3) = Sheet1.Cells(rowRead, 5).Value
    Sheet2.Cells(rowWrite, 4) = Sheet1.Cells(rowRead, 6).Value
    Sheet2.Cells(rowWrite, colWrite) = Sheet1.Cells(rowRead, 3).Value

    prevStudent = currStudent

Next rowRead
End Sub

相关内容