筛选出表1中不包括表2身份证号码的数据。

losesc

筛选出表1中不包括表2身份证号码的数据。但countif在计数身份证号码时出现错误（前15位相同的就认为相同），两个参数都是区域无法加&“*”。如何解决？

满坛皆为吾师

COUNTIF二参改一下
Sheet1!B2:B21&"*"

dambcer

静听溪流

=FILTER(Sheet1!A2:F21,COUNTIF(Sheet2!B2:B7,Sheet1!B2:B21&"*")=0,"")

inrever

1. =FILTER(Sheet1!A2:F21,ISNA(VLOOKUP(Sheet1!B2:B21,Sheet2!B:C,1,)))

复制代码

mykitami

=FILTER(Sheet1!A2:F21,COUNTIF(Sheet2!B2:B7,Sheet1!B2:B21&"*")=0,"")

zhangcheng6688

学习一下字典，打扰了
Sub test()
arr = Sheet2.[a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
   d(arr(i, 2)) = ""
Next
brr = Sheet1.[a1].CurrentRegion
ReDim crr(1 To UBound(brr), 1 To UBound(brr, 2))
For i = 2 To UBound(brr)
   If Not d.exists(brr(i, 2)) Then
      m = m + 1
      For j = 1 To UBound(brr, 2)
         crr(m, j) = brr(i, j)
      Next
   End If
Next
Sheet3.[a2].Resize(m, UBound(crr, 2)) = crr
End Sub

飞天篮球猪