Large Data set. Unique values Formula

[ItalianPlatinum]

Hello - I have a dataset where there will be 39 unique results, but they could repeat up to 100k times on the excel. I need a formula to strip me out of all that data and just give me how many unique results are there. I found the below formula to work but it is WAY TOO SLOW to calculate. is there a faster way to get what i need? the -7 is because above the data i have 7 fields of unrelated data. was hoping a formula did not want to go the VBA or remove duplicates route.

=SUMPRODUCT((A:A <> "")/COUNTIF(A:A,A:A & ""))-7

Thanks in advance

 

[CA_Punit]

You can use countif by adding a helper column

Book1
	B	C
4	INVOICE	Count if
5	INA2009004	1
6	INA2009006	1
7	INA2009004	2
8	INA2009004	3
9	INA2009005	1
10	INA2009006	2
11	INA2009007	1
12	INA2009008	1
13	INA2009009	1
Sheet2

Cell Formulas
Range	Formula
C5:C13	C5	=COUNTIF($B$4:B5,B5)

If the value is greater than 1 then it is duplicate then you can use a Pivot or Filter Option also


Also, You can draw a Pivot table to find the Unique Values

 

[ItalianPlatinum]

You can use countif by adding a helper column

Book1
	B	C
4	INVOICE	Count if
5	INA2009004	1
6	INA2009006	1
7	INA2009004	2
8	INA2009004	3
9	INA2009005	1
10	INA2009006	2
11	INA2009007	1
12	INA2009008	1
13	INA2009009	1
Sheet2

Cell Formulas
Range	Formula
C5:C13	C5	=COUNTIF($B$4:B5,B5)

If the value is greater than 1 then it is duplicate then you can use a Pivot or Filter Option also


Also, You can draw a Pivot table to find the Unique Values

Trying to go away from a table cause te unique values could change overtime. so making it dummy proof or trying to

 

[CA_Punit]

Trying to go away from a table cause te unique values could change overtime. so making it dummy proof or trying to

Sorry i Didn't Understand

 

[Fluff]

Try limiting the range of the formula, you should avoid using entire columns when using arrays.
=SUMPRODUCT((A5:A100000 <> "")/COUNTIF(A5:A100000,A5:A100000 & ""))

 

[ItalianPlatinum]

Try limiting the range of the formula, you should avoid using entire columns when using arrays.
=SUMPRODUCT((A5:A100000 <> "")/COUNTIF(A5:A100000,A5:A100000 & ""))

getting this error message

#NAME?

 

[Fluff]

I just used the formula you supplied & limited the ranges.
So you would only get that error if you mistyped the formula, or you have a #NAME? error somewhere in col A.

 

[ItalianPlatinum]

I just used the formula you supplied & limited the ranges.
So you would only get that error if you mistyped the formula, or you have a #NAME? error somewhere in col A.

odd because if i do A:A it works but if i limit it then it doesn't produce results. I can confirm #NAME? doesnt exist anywhere in column A

 

[ItalianPlatinum]

I stumbled on this and so far it looks to be running quickly and yielding the results I need

=CountUniqueValues(A:A)-7

Public Function CountUniqueValues(rng As Variant) As Variant
Dim Test As New Collection
Dim Value As Variant
rng = rng.Value
On Error Resume Next
For Each Value In rng
If Len(Value) > 0 Then Test.Add Value, CStr(Value)
Next Value
On Error GoTo 0
CountUniqueValues = Test.Count
End Function

 

[Fluff]

Glad you sorted it & thanks for the feedback.