vba - Excel VBA - 带有逗号分隔数据的单元格已排序

Question

我有数据:

    A          B

1  RED   him, her, kirk

2  BLUE  kirk, rose, jill

我想按 B 列排序，为 B 列中以逗号分隔的每个数据点创建行，以输出:

    C       D

1  her     RED

2  him     RED

3  jill    BLUE

4  kirk    RED

5          BLUE

6  rose    BLUE

你能帮我解决这个问题吗？我假设我需要 excel 来查看第 2 列中的每个单元格，为每个逗号分隔的数据点创建数据库，然后将第 1 列颜色添加到每个数据库中。我不知道该怎么做并输出它。

Answer 1

尝试在 SuperUser 上阅读相同的问题

我假设您已将文件插入到 excel 中:

Sub test()

Set iWsh = WorkSheets("Sheet1") 'Sheet containing raw data (text-file)
Set oWsh = Worksheets("Sheet2") 'Sheet you wanted to have processed data in it

Set rng = [B1]
Set rng = Range(rng, Cells(Rows.Count, rng.Column).End(xlUp))

 rng.TextToColumns Destination:=rng, DataType:=xlDelimited, _
                   ConsecutiveDelimiter:=False, Tab:=False, _
                   Semicolon:=False, Comma:=True, Space:=False, Other:=False

i = 1
j = 2
k = 1

  While iWsh.Cells(i,1) <> "" ' Loops through rows of the data
    Do While iWsh.Cells(i,j) <> "" 'Loops through column of the data 

       oWsh.Cells(k,1) = iWsh.Cells(i,j)

       oWsh.Cells(k,2) = iWsh.Cells(i,1)

      k = k + 1
      j = j + 1

    Loop

     i = i + 1
     j = 2

  Wend

End Sub