excel - 从一对多工作表创建多对一工作表(excel - Create a many-to-one sheet from a one-to-many sheet)

 我在Excel中有一张表，类似于： 
 
| Value1 | Data1 | Data1b | 1,3,4,8 |
| Value2 | Data2 | Data2b | 2       |
| Value3 | Data3 | Data3b | 6,7,8   |
 
 我想把那张表格做成另一张表格，将最后一列分成不同的行并保持其他数据同步。 因此，当第一张纸更新时，第二张纸也会更新。 如果在第一张表中的最后一列添加了一个数字，则会在第二张表中添加一个新行。 
 
 第二张表应如下所示： 
 
| Value1 | Data1 | Data1b | 1 |
| Value2 | Data2 | Data2b | 2 |
| Value1 | Data1 | Data1b | 3 |
| Value1 | Data1 | Data1b | 4 |
| Value3 | Data3 | Data3b | 6 |
| Value3 | Data3 | Data3b | 7 |
| Value1 | Data1 | Data1b | 8 |
| Value3 | Data3 | Data3b | 8 |
 
 更新 ：下面是我试图使用的代码。 首先，我有最好的方法吗？ 然后清理然后重新填充正确的方法来更新第二张表格？ 最后，如何在更新第一张纸时自动运行？ 
 
 更新 ：唯一仍然不起作用的是最后的那种，有没有人知道为什么？ 
 
Private FROM_SHEET As String
Private TO_SHEET As String
Private START_ROW As Long
Private NUM_COL As Long

Sub oneToMany()

    FROM_SHEET = "Sheet1"
    TO_SHEET = "Sheet2"
    START_ROW = 2
    NUM_COL = 4

    Dim fromSheet As Worksheet
    Dim toSheet As Worksheet
    Dim newRow As Long

    Set fromSheet = Sheets(FROM_SHEET)
    Set toSheet = Sheets(TO_SHEET)

    toSheet.UsedRange.ClearContents

    newRow = START_ROW
    For i = START_ROW To fromSheet.Cells(fromSheet.Rows.Count, 1).End(xlUp).Row
        Dim col As String
        Dim nums() As String

        col = fromSheet.Cells(i, NUM_COL)
        nums = Split(col, ",")

        For Each num In nums
            fromSheet.Rows(i).Copy toSheet.Rows(newRow)
            toSheet.Cells(newRow, NUM_COL) = Trim(num) 'Should copy then overwrite?
            newRow = newRow + 1
        Next num
    Next

    'Sort not working
    toSheet.Range(toSheet.Cells(START_ROW, START_COL), toSheet.Cells(lastRow, lastCol)).Sort _
        key1:=toSheet.Range(toSheet.Cells(START_ROW, NUM_COL), toSheet.Cells(lastRow, NUM_COL)), _
        order1:=xlAscending, Header:=xlNo

End Sub

I have a sheet in Excel, Something like:
 
| Value1 | Data1 | Data1b | 1,3,4,8 |
| Value2 | Data2 | Data2b | 2       |
| Value3 | Data3 | Data3b | 6,7,8   |
 
I'd like to take that sheet and make another that divides up that final column into separate rows and keeps the other data in sync. So when the first sheet is updated, the second is also updated. And if a number is added to that final column in the first sheet, a new row is added to the second sheet.
 
The second sheet should look like this:
 
| Value1 | Data1 | Data1b | 1 |
| Value2 | Data2 | Data2b | 2 |
| Value1 | Data1 | Data1b | 3 |
| Value1 | Data1 | Data1b | 4 |
| Value3 | Data3 | Data3b | 6 |
| Value3 | Data3 | Data3b | 7 |
| Value1 | Data1 | Data1b | 8 |
| Value3 | Data3 | Data3b | 8 |
 
UPDATE: Below is the code I'm attempting to use. First of all, is what I have the best way? And is clearing then repopulating the right way to go about updating the second sheet? Finally, how do I make this automatically run when one updates the first sheet?
 
UPDATE: The only thing still not working is the sort at the end, does anyone have any idea why?
 
Private FROM_SHEET As String
Private TO_SHEET As String
Private START_ROW As Long
Private NUM_COL As Long

Sub oneToMany()

    FROM_SHEET = "Sheet1"
    TO_SHEET = "Sheet2"
    START_ROW = 2
    NUM_COL = 4

    Dim fromSheet As Worksheet
    Dim toSheet As Worksheet
    Dim newRow As Long

    Set fromSheet = Sheets(FROM_SHEET)
    Set toSheet = Sheets(TO_SHEET)

    toSheet.UsedRange.ClearContents

    newRow = START_ROW
    For i = START_ROW To fromSheet.Cells(fromSheet.Rows.Count, 1).End(xlUp).Row
        Dim col As String
        Dim nums() As String

        col = fromSheet.Cells(i, NUM_COL)
        nums = Split(col, ",")

        For Each num In nums
            fromSheet.Rows(i).Copy toSheet.Rows(newRow)
            toSheet.Cells(newRow, NUM_COL) = Trim(num) 'Should copy then overwrite?
            newRow = newRow + 1
        Next num
    Next

    'Sort not working
    toSheet.Range(toSheet.Cells(START_ROW, START_COL), toSheet.Cells(lastRow, lastCol)).Sort _
        key1:=toSheet.Range(toSheet.Cells(START_ROW, NUM_COL), toSheet.Cells(lastRow, NUM_COL)), _
        order1:=xlAscending, Header:=xlNo

End Sub

原文：https://stackoverflow.com/questions/41747593