在Excel工作表中检测重复的功能 [英] Function for detecting duplicates in Excel sheet

问题描述

我需要一个可以检测指定Excel列中的重复项的功能。
我有这个，但它不能正常工作。它不能区分值46.500和值46.5000。 countif函数可能将单元格作为数字进行比较。这些单元格格式为文本，甚至尝试在数字之前添加一个撇号。没有运气。

 函数check_duplicates（列As String）
 LastRow = Range（column&65536）。 End（xlUp）.row 
对于x = LastRow到1 Step -1 
 
如果Application.WorksheetFunction.CountIf（Range（column&1：& column& LastRow） ，Range（column& x）.Text）> 1然后
 check_duplicates = x'返回行重复
x = 1 
 Else 
 check_duplicates = 0 
结束如果
下一个x 
结束函数
  

catch是Lineif的行。

有没有人知道如何强制使用VBA CountIf函数来比较单元格的字符串或其他方法来检查VBA中的重复项？

解决方案

p>我通常在这种情况下发现有用的东西。

  Dim cn As Object 
 Dim rs As Object 
 
 strFile =工作簿（1）.FullName 
 strCon =Provider = Microsoft.Jet.OLEDB.4.0; Data Source =& strFile _ 
& ;扩展属性=Excel 8.0; HDR =否; IMEX = 1; 
 
设置cn = CreateObject（ADODB.Connection）
设置rs = CreateObject（ADODB.Recordset）
 
 cn.Open strCon 
 
 strSQL =SELECT F2，Count（F2）AS CountF2 FROM [Sheet1 $]_ 
& GROUP BY F2 HAVING Count（F2）> 1
 rs.Open strSQL，cn 
 
s = rs.GetString 
 MsgBox s 
 
' '或
表单（Sheet2）。单元格（2，1）.CopyFromRecordset rs 
  

I need a function that can detect duplicates in a specified Excel column. I have this one but it does not work properly. It cannot distinguish between the value "46.500" and the value "46.5000". The countif function probably compares cells as numbers. These cells are formatted as text and I have even tried to add an apostrophe prior the numbers. No luck.

Function check_duplicates(column As String)
LastRow = Range(column & "65536").End(xlUp).row
For x = LastRow To 1 Step -1

    If Application.WorksheetFunction.CountIf(Range(column & "1:" & column & LastRow), Range(column & x).Text) > 1 Then
        check_duplicates = x  ' return row with a duplicate
        x = 1   
    Else
         check_duplicates = 0
    End If
Next x
End Function

The catch is the line with Countif.

Does anyone know how to force VBA CountIf function to compare cells as strings or other way to check for duplicates in VBA?

解决方案

I usually find ado useful in such circumstances.

Dim cn As Object
Dim rs As Object

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT F2, Count(F2) AS CountF2 FROM [Sheet1$] " _
  & "GROUP BY F2 HAVING Count(F2)>1 "
rs.Open strSQL, cn

s = rs.GetString
MsgBox s

'' Or
Sheets("Sheet2").Cells(2, 1).CopyFromRecordset rs

这篇关于在Excel工作表中检测重复的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！