I have a large amount of data (about 1,000,000 rows of data). What is the fastest way to find unique values for one column?

[Edit] I am not sure about how data repeats a lot or there are many unique values. I only want unique. Can remove duplicate values in original set. Or take the unique set and copy to column next to it.

Yes, 1,000,000 rows is a lot, but I cannot use any database to help out. [/Edit]

I know the Data ribbon, Advanced option. It is slow and seems looping for sometime.

My excel is 2013 version.


  • You mean there is a lot of duplicated values in this column and a few unique (non-duplicated), and you want to find only the second ones list? What output form you need - copy to another column, highlight, hide rows with duplicated values, etc.? – Akina Jul 20 '18 at 4:51
  • Data Ribbon -> Remove Duplicates will be the fastest way. – PeterH Jul 20 '18 at 7:40
  • Since you have specified the Fastest way to find unique values from large amount of data so that I've suggested you one simple Macro, just check it, is very easy to use, the alternative is Formula ! – Rajesh S Jul 20 '18 at 8:43
  • 1
    Your use of the singular "value" leads to different possible interpretations of your question. Are you looking to eliminate duplicates from the list, leaving a single copy of every different value, or as Akina suggested, identify the items that already exist only as unique values (or the single such case), and ignore values for which there are duplicates? BTW, 1,000,000 rows of data will take some time to process in Excel. If you're dealing with data on that scale, it might make sense to do this in a database. – fixer1234 Jul 20 '18 at 19:36
  • @Akina I want to get a list of unique values. This list can be in a new column, or remove duplicates in original list/set. – user3454439 Jul 23 '18 at 2:10

The fastest method to find & extract Unique values for Large data range is, MACRO (VBA).

enter image description here

Insert this code as standard Module with the sheet.

Option Explicit

Sub GetUniqueList()

Dim d As Object, c As Variant, i As Long, lr As Long
Set d = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, 1).End(xlUp).Row
c = Range("A2:A" & lr)
For i = 1 To UBound(c, 1)
  d(c(i, 1)) = 1
Next i
Range("B2").Resize(d.Count) = Application.Transpose(d.keys)
End Sub

How it works:

  1. In VB Editor click Tool menu & hit References and check whether Microsoft Scripting Runtime is checked or not, check it if not.
  2. Back to sheet & Run the Macro.
  3. It checks Duplicates in column A & output Unique in column B.

You find the list of unique values in Column B.

Note, adjust cell references in code as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.