如何删除 Excel 中区分大小写的重复项(对于 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 列 = 显示数据的列(来自 A 列)如果唯一，否则为空字符串

但是我遇到了这个问题:

However I hit this issue:

是的，我的 Excel 2016 是 32 位的，是的，我的笔记本电脑只有 8GB 内存.但是我读到 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 函数中有一个函数:数据 > 选择列> 删除重复项.但是这个函数只删除大小写不敏感的数据.

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

编辑代码:

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 中区分大小写的重复项(对于 100k 记录或更多)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！