检测格式为文本的重复项 [英] Detecting duplicates formatted as text

问题描述

我需要一个功能来检测格式为文本的重复项.

I need a function to detect duplicates formatted as text.

这不能区分"46.500"和"46.5000". CountIf可能会将单元格作为数字进行比较.这些单元格被格式化为文本.我试图在数字前加撇号.

This cannot distinguish between "46.500" and"46.5000". CountIf probably compares cells as numbers. These cells are formatted as text. I tried to add an apostrophe prior the numbers.

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

有人知道如何强制CountIf将单元格作为字符串进行比较或以其他方式检查VBA中的重复项吗?

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

推荐答案

在这种情况下，我通常会发现ado很有用.

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

这篇关于检测格式为文本的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！