execl在多张表格中查询某列id是否重复

swl4561255

1. <div class="blockcode"><blockquote>
   

复制代码

Sub test()

ar = [A1].End(4).Row

br = [C1].End(4).Row

For i = 2 To ar

  s = ""

  For j = 2 To br

    If IsNumeric(Application.Match(Cells(i, 1), Sheets(Cells(j, 3).Value).Range("A:A"), 0)) Then

      s = s & "," & Cells(j, 3)

    End If

  Next

  Cells(i, 2) = Mid(s, 2) & "表中有重复项！"

Next

End Sub

swl4561255

上面代友有点问题，改了一下：

1. Sub test()
   
2. ar = [A1].End(4).Row
   
3. br = [C1].End(4).Row
   
4. For i = 2 To ar
   
5.   s = ""
   
6.   For j = 2 To br
   
7.     If IsNumeric(Application.Match(Cells(i, 1), Sheets(Cells(j, 3).Value).Range("A:A"), 0)) Then
   
8.       s = s & "," & Cells(j, 3)
   
9.     End If
   
10.   Next
    
11.   Cells(i, 2) = Mid(s, 2) & "表中有重复项！"
    
12. Next
    
13. End Sub
    

复制代码

啸海

=IF(SUM(IFERROR(COUNTIF(INDIRECT(C$2:C$99&"!a:a"),A2),)),"有","")

袁勇

5101314zt 发表于 2024-3-20 09:47
比如61这个数字，在本表sheet10如果出现两次及以上，提示有重复；如果61这个数字，在sheet1-sheet9任一一 ...

？？？

=IF(OR(COUNTIF(A:A,A2)>1,SUMPRODUCT(COUNTIF(INDIRECT(C$2:C$10&"!a:a"),A2))>0),"重复","")