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:

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.

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