excelexcel-formulaexcel-2016

How to remove duplicates that are case SENSITIVE in Excel (for 100k records or more)?

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

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

where:

  1. column A = column with all data including duplicates
  2. column B = column to display data (from column A) if unique otherwise empty string

However I hit this issue:

enter image description here

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.

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

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.

Solution

  • 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

    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