问题描述
我已尝试使用此公式字段并复制到我拥有的所有 >100k 记录.
I have tried using this formula field and copying to all >100k records I have.
=IF(SUMPRODUCT(--EXACT(A2,$B$1:B1)),"",A2)
哪里:
- 列 A = 包含所有数据的列,包括重复项
- 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 记录或更多)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!