一个宏在一个SINGLE列中找到重复的建议 [英] suggestions for a macro to find duplicates in a SINGLE column

问题描述

发现很多问题涉及在两列中找到重复的问题：

found a lot of questions involving finding duplicates in two columns :

即。
MS Excel如何创建宏以找到重复项并突出显示？和 excel标记重复值

然而，我正在尝试调整代码以用于在一列中查找重复项。例如，这里是一个数据集：

However I'm trying to adapt code to be used to find duplicates in one column. For example here is a data set:

列1

Foo

Bar < br>
23

23

12

foo

酒吧

bart

Foo
Bar
23
23
12
foo
Bar
bart

这是我现在正在使用的：

This is what I'm using right now:

Function warnDupes()

Dim lastRow As Long
Dim dict As Object

' Let Col be the column which warnDupes operates on.
Dim Col As String

Col = "A"

Set dict = CreateObject("scripting.dictionary")

lastRow = range(Col & Rows.Count).End(xlUp).Row

On Error Resume Next
For i = lastRow To 1 Step -1
    If dict.Exists(range(Col & i).value) = True Then

    'range("Y" & i).EntireRow.Delete

    MsgBox ("Hmm...Seems to be a duplicate of " & range(Col & i).value & _
    " in Cell " & Col & i)

End If
dict.Add range(Col & i).value, 1
Next

End Function

到目前为止，我有一些代码可以完成90％的工作。 23和23匹配。酒吧和酒吧配对。所以代码匹配字符串和Ints。但是我希望宏可以匹配Foo和foo作为重复。如何使Excel忽略大小写？

So far I've got some code that does 90% of the job. 23 and 23 are matched. Bar and Bar are matched. etc. So the code matches both Strings and Ints. But I'd like the macro to be able to match Foo and foo as a duplicate as well. How do I make Excel ignore case?

此问题（在Excel工作表中检测重复的功能）似乎是相关的，但我无法调整代码或了解作者的作者。对代码，解释或建议的任何改进将非常感谢。

This question ( Function for detecting duplicates in Excel sheet ) seems relevent but I'm having trouble adapting the code or understanding what the author did. Any improvements to the code, explanations or suggestions would be very much appreciated.

谢谢。

更新：

只是注意到一些真的很奇怪。

Just noticed something really weird.

数据：

IB6061

IC6071

IB6061
IC6071

是否匹配，无论我使用我的宏还是我在Excel中使用条件格式化工具。有什么原因吗

are matched whether I use my Macro or if I use the Conditional Formatting tool in Excel. Any reason why?

推荐答案

在你的存在（）& .Add（）行，使这两个值相同的情况：

On your Exists() & .Add() lines, make both values the same case:

If dict.Exists(UCase$(Range(Col & i).Value)) Then

和

dict.Add UCase $（Range（Col& i）.Value），1

这些重复项将始终以大写字母添加到字典中，因此案例将不会重要。

That way the duplicates will always be added to the dictionary in uppercase and so case will never matter.

这篇关于一个宏在一个SINGLE列中找到重复的建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！