vba - 使用 VBA 同步两个列表

Question

同步两个列表(每个列表可能包含另一个列表中不包含的项目)的最佳方法是什么？如图所示，列表未排序 - 尽管如果有必要，首先对它们进行排序不会成为问题。

List 1 = a,b,c,e
List 2 = b,e,c,d

使用上面的列表，我正在寻找一种可以分两列写入电子表格的解决方案:

a
b  b
c  c
   d
e  e

Answer 1

以下是有关使用断开连接的记录集的一些注意事项。

Const adVarChar = 200  'the SQL datatype is varchar

'Create arrays fron the lists
asL1 = Split("a,b,c,", ",")
asL2 = Split("b,e,c,d", ",")

'Create a disconnected recordset
Set rs = CreateObject("ADODB.RECORDSET")
rs.Fields.append "Srt", adVarChar, 25
rs.Fields.append "L1", adVarChar, 25
rs.Fields.append "L2", adVarChar, 25

rs.CursorType = adOpenStatic
rs.Open

'Add list 1 to the recordset
For i = 0 To UBound(asL1)
    rs.AddNew Array("Srt", "L1"), Array(asL1(i), asL1(i))
    rs.Update
Next

'Add list 2
For i = 0 To UBound(asL2)
    rs.MoveFirst
    rs.Find "L1='" & asL2(i) & "'"

    If rs.EOF Then
        rs.AddNew Array("Srt", "L2"), Array(asL2(i), asL2(i))
    Else
        rs.Fields("L2") = asL2(i)
    End If

    rs.Update
Next

rs.Sort = "Srt"

'Add the data to the active sheet
Set wks = Application.ActiveWorkbook.ActiveSheet

rs.MoveFirst

intRow = 1
Do
    For intField = 1 To rs.Fields.Count - 1
        wks.Cells(intRow, intField + 1) = rs.Fields(intField).Value
    Next intField

    rs.MoveNext
    intRow = intRow + 1
Loop Until rs.EOF = True