如何清除在Excel中为SENSITIVE发送的重复项（100k或更多记录）？ [英] How to remove duplicates that are case SENSITIVE in Excel (for 100k records or more)?

问题描述

我已尝试使用此公式字段并复制到所有> 100k的记录。

I have tried using this formula field and copying to all >100k records I have.

= IF（SUMPRODUCT（ - EXACT） （A2，$ B $ 1：B1）），，A2）

=IF(SUMPRODUCT(--EXACT(A2,$B$1:B1)),"",A2)

其中：

1. 列A =所有数据包括重复项的列


2. 列B =显示数据的列（如果为空，则为空）

但是我遇到这个问题：

是的，我的Excel 2016是32位，是的，我的笔记本电脑只有8GB RAM。但是我已经阅读了64位和16GB RAM的人都遇到了与我一样的错误。

Yes my Excel 2016 is 32bit and yes my laptop is only 8GB RAM. But I have read up that people with 64bit and 16GB RAM experienced the same error as me.

我知道Excel功能有一个功能：数据>选择列）>删除重复。但是，此功能仅删除案例INSENSITIVE数据。

I know there is a function in Excel function : Data > Select Column(s)> Remove Duplicates. However this function deletes case INSENSITIVE data only.

请告知我如何克服这个问题。我可以使用Crystal Reports或某种免费软件来解决这个问题。请指教。

Please advise me how I can overcome this issue. I am open to using stuff like Crystal Reports or some sort of freeware to solve this issue. Please advise.

推荐答案

你可以尝试这样的事情。
在尝试此备份您的数据之前。
以下代码将从列A中删除重复项，区分大小写。

You may try something like this. Before trying this backup your data. The code below will remove the duplicates from the column A and it is case sensitive.

Sub GetUniqueValues()
Dim x, dict
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
x = Range("A2:A" & lr).Value
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 1)
    dict.Item(x(i, 1)) = ""
Next i
Range("A2:A" & lr).ClearContents
Range("A2").Resize(dict.Count).Value = Application.Transpose(dict.keys)
End Sub

已编辑代码 p>

Edited Code:

Sub GetUniqueValues()
Dim x, dict, y
Dim lr As Long
Application.ScreenUpdating = False

lr = Cells(Rows.Count, 1).End(xlUp).Row
x = Range("A2:A" & lr).Value
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 1)
    dict.Item(x(i, 1)) = ""
Next i
ReDim y(1 To dict.Count, 1 To 1)

i = 0
For Each it In dict.keys
   i = i + 1
   y(i, 1) = it
Next it

Range("A2:A" & lr).ClearContents
Range("A2").Resize(dict.Count).Value = y

Application.ScreenUpdating = True
End Sub

这篇关于如何清除在Excel中为SENSITIVE发送的重复项（100k或更多记录）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！