我看到您不介意放弃VBA，但愿意使用公式.使用microsoft365，您可以使用:

C2

中的公式

  = UNIQUE(FILTER(A2:INDEX(A:A，MATCH("ZZZ''，A:A)))，COUNTIF(B2:INDEX(B:B，MATCH("ZZZ''''，B:B))，A2:INDEX(A:A，MATCH("ZZZ"，A:A)))= 0)) 

---

如果您确实想通过VBA，则可以使用字典.一个简单的例子可能是:

  Sub Test()昏暗的LrA长，LrB长，x长昏暗的arrA作为变体，arrB作为变体将Dim dict作为对象:设置dict = CreateObject("Scripting.Dictionary")昏暗的ws作为工作表:设置ws = ThisWorkbook.Worksheets("Sheet1")与ws'获取上次使用的行LrA = .Cells(.Rows.Count，1).End(xlUp).RowLrB = .Cells(.Rows.Count，2).End(xlUp).Row'初始化数组arrA = .Range("A2:A"& LrA).arrB = .Range("B2:B"& LrB).'在arrA上运行并填写字典对于x = LBound(arrA)到UBound(arrA)dict(arrA(x，1))= 1下一个'在arrB上运行并从字典中删除对于x = LBound(arrB)到UBound(arrB)如果dict.Exists(arrB(x，1))然后dict.Remove arrB(x，1)下一个'从字典中拉出余数.Cells(2，3).Resize(dict.Count).Value = dict.Keys结束于结束子 

There are a few questions that ask something similar but not the exact thing.

I have two columns X and Y. Y contains only values that exist in X. I want to create a column Z that has all the values that exist only in X.

• X and Y can contain duplicate data as shown in the example
• X exists in sheet1 whilst Y and Z exist in sheet2

So far, I recorded a macro so naturally the code is super slow, despite my best efforts to clean it up. I won't post the whole code because it's quite messy but essentially I've

1. Used the unique() function to create two columns that contain the unique values of X and Y respectively.

2. Used vlookup() to create an adjacent column to the two I just created that returns an empty string if the adjacent unique X value exists in the unique Y column else returning the X value. This part is horribly slow. I created the formula in one cell then pasted it down.

Range("U2").Formula2R1C1 = "=UNIQUE('1.HoldingCart'!C[-18])"
Range("V2").Formula2R1C1 = "=UNIQUE(C[-19])"
Range("W3").FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2], C[-1], 1, FALSE)), RC[-2], """")"
Range("W3").Copy
Range("W3:W" & Cells(Rows.Count, "U").End(xlUp).Row).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

3. Filtered out all the empty strings on the vlookup() column. Copied the actual values. Got rid of the filter. Deleted everything and then pasted the copied data thus creating column Z.

' Get the discrepancies
ActiveSheet.Range("$W:$W").AutoFilter Field:=1, Criteria1:="<>"
Range("W2:W" & Cells(Rows.Count, "W").End(xlUp).Row).Copy
Range("X2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False

' Clean the sheet
ActiveSheet.ShowAllData
Selection.AutoFilter
Range("U2:W" & Cells(Rows.Count, "W").End(xlUp).Row).ClearContents

' Paste the discrepancies
Range("X2:X" & Cells(Rows.Count, "X").End(xlUp).Row).Cut
Range("U2").Select
ActiveSheet.Paste

Sorry you just had to read that horrible code. I'm happy to throw all that away. Any help would be appreciated.

I see you do not mind to let go of VBA, but are willing to use a formula instead. With microsoft365, you can use:

Formula in C2

=UNIQUE(FILTER(A2:INDEX(A:A,MATCH("ZZZ",A:A)),COUNTIF(B2:INDEX(B:B,MATCH("ZZZ",B:B)),A2:INDEX(A:A,MATCH("ZZZ",A:A)))=0))

---

If you do want to go through VBA, then maybe use a Dictionary. A crude example could be:

Sub Test()

Dim LrA As Long, LrB As Long, x As Long
Dim arrA As Variant, arrB As Variant
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")

With ws
    
    'Get last used rows
    LrA = .Cells(.Rows.Count, 1).End(xlUp).Row
    LrB = .Cells(.Rows.Count, 2).End(xlUp).Row
    
    'Initialize arrays
    arrA = .Range("A2:A" & LrA).Value
    arrB = .Range("B2:B" & LrB).Value
    
    'Run over arrA and fill Dictionary
    For x = LBound(arrA) To UBound(arrA)
        dict(arrA(x, 1)) = 1
    Next
    
    'Run over arrB and remove from Dictionary
    For x = LBound(arrB) To UBound(arrB)
        If dict.Exists(arrB(x, 1)) Then dict.Remove arrB(x, 1)
    Next
    
    'Pull remainder from dictionary
    .Cells(2, 3).Resize(dict.Count).Value = dict.Keys
    
End With

End Sub