Count unique values

[nikhil0311]

Hello Friends,
I need a formula to show unique count in column F as 0 or 1 based on key present in column E. Note that column E is not sorted.
Given that I have 0.5 million rows data, need any effective formula that will not impact the performance of the excel file.

Thanks a lot in advance to this great forum!!

 

[DanteAmor]

Try this:

Libro1
	A	B	C	D	E	F
1	ID	Prod	LOB	Stat	Key	Uni
2	12	rec	CRE	ACT	12recCREACT	1
3	66	ca	CC	ACT	66caCCACT	1
4	12	ca	CRE	ACT	12caCREACT	1
5	66	ca	CC	ACT	66caCCACT	0
6	12	ca	CRE	ACT	12caCREACT	0
7	12	rec	CRE	ACT	12recCREACT	0
Hoja2

Cell Formulas
Range	Formula
F2:F7	F2	=IF(COUNTIF($E$2:E2,E2)=1,1,0)

If you have problems with the performance, then you should try a macro.

 

[Peter_SSs]

Your question is not entirely clear to me. Your sample shows a series of 0 & 1 values in column F but your thread title indicates a count of the unique values is wanted. Also, I'm wondering why F7 is populated with a 1 and not F2?

Given that I have 0.5 million rows data, need any effective formula that will not impact the performance of the excel file.

I doubt that you will find one. For example, with only 100,000 rows with my test data, the formula suggested by Dante took over 4 minutes to calculate the column. The following formula 'only' took 43 seconds. Admittedly my machine is quite old and a newer, more powerful one may be considerably quicker, but I would say the time is still far too slow.

=--ISNA(MATCH(E2,E$1:E1,0))

Therefore I am agreeing with Dante that a macro is likely a much better option.

If you actually want all the 0/1 values in the adjacent column, then you might consider this one (0.25 seconds for the same 100,000 rows of data)

Sub Test1()
  Dim d As Object
  Dim a As Variant
  Dim i As Long

  Set d = CreateObject("Scripting.Dictionary")
  With Range("E2", Range("E" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      If d.exists(a(i, 1)) Then
        a(i, 1) = 0
      Else
        d(a(i, 1)) = 1
        a(i, 1) = 1
      End If
    Next i
    .Offset(, 1).Value = a
  End With
End Sub

If it is just the count of unique items that you want then try

Sub Test2()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("E2", Range("E" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = 1
  Next i
  Range("G1").Value = d.Count
End Sub