比较A和B列，并创建新列，其中包含仅在A列中存在的值 [英] Compare column A and B and create new column that contains values that only exist in column A

问题描述

有些问题会问类似但并非确切的问题.

我有两列 X 和 Y . Y 仅包含 X 中存在的值.我想创建一列 Z ，它具有仅存在于 X 中的所有值.

如示例所示，
• X 和 Y 可以包含重复数据
• X 存在于 sheet1 中，而 Y和Z 存在于 sheet2
中

<身体>

X	Y	Z
a	c	a
b	e	b
b	d
c	e
d
e

到目前为止，尽管我尽了最大努力来清理宏，但我还是自然地记录了一个宏，代码非常慢.我不会发布整个代码，因为它很乱，但本质上我已经

1. 使用 unique()函数创建两列，分别包含 X 和 Y 的唯一值.

>

2. 使用 vlookup()创建与我刚刚创建的两个列相邻的列，并返回一个空字符串如果相邻的唯一 X 值存在于唯一的 Y 列中，则返回 X 值.这部分太慢了.我在一个单元格中创建了公式，然后将其粘贴下来.

  Range("U2").Formula2R1C1 ="= UNIQUE('1.HoldingCart'！C [-18])'范围("V2").公式2R1C1 ＝"＝ UNIQUE(C [-19])".范围("W3").公式R1C1 ＝"＝ IF(ISNA(VLOOKUP(RC [-2]，C [-1]，1，FALSE))"，RC [-2]，"";)范围("W3").复制范围("W3:W"& Cells(Rows.Count，"U").End(xlUp).Row).PasteSpecial Paste:= xlPasteFormulas，Operation:= xlNone，SkipBlanks:= False，Transpose:= False 

3. 过滤掉 vlookup()列上的所有空字符串.复制了实际值.摆脱了过滤器.删除所有内容，然后粘贴复制的数据，从而创建列 Z .

 '获取差异ActiveSheet.Range("$ W:$ W").AutoFilter字段:= 1，条件1:=<>"；范围("W2:W"& Cells(Rows.Count，"W").End(xlUp).Row).Copy范围("X2").PasteSpecialPaste:＝ xlPasteValues，操作:＝ xlNone，SkipBlanks _:＝ False，转置:＝ False'清洁床单ActiveSheet.ShowAllDataSelection.AutoFilter范围("U2:W"& Cells(Rows.Count，"W").End(xlUp).Row).ClearContents'粘贴差异范围("X2:X"& Cells(Rows.Count，"X").End(xlUp).Row).Cut范围("U2").选择ActiveSheet.Paste 

对不起，您只需要阅读该可怕的代码.我很乐意将所有这些扔掉.任何帮助将不胜感激.

解决方案

我看到您不介意放弃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

X	Y	Z
a	c	a
b	e	b
b	d
c	e
d
e

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

这篇关于比较A和B列，并创建新列，其中包含仅在A列中存在的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！