Find the unique value "excluding empty cells"

[Sam_D_Ben]

Excel 2007
	A
1	Products
2	Name1
3	Name1
4	Name1
5	Name2
6	Name2
7	Name2
8
9	Name3
10	Name3
11	Name3
Sheet1

Kindly find the above sample date; where i need to find the unique value "excluding empty cells" (In this its 3Nos). Like wise i got around lacs of datas getting updated daily; means the whole column"A" should be selected. Is there any way to get the count fast....i mean to reduce the calculation timings; Thansks in advance ....Muz

 

[T. Valko]

Hi Biff,

Lakh is used primarily on the Indian Sub continent.

One Lakh = 100,000
Ten Lakh = 1,000,000

OK, let me go test those formulas on 1 million rows of data...

 

[Sam_D_Ben]

I am sorry Mr T Valko .... I mean 1 Million rows...! I am working in a telecom firm, At time i used to import datas from Ms.Access...Thats it. As sorry if it confused you

But the way ....the above formula is just confusing ...so am just watching that Youtube video...as per the request from Mr Vidar. Thank You

 

[T. Valko]

I am sorry Mr T Valko .... I mean 1 Million rows...! I am working in a telecom firm, At time i used to import datas from Ms.Access...Thats it. As sorry if it confused you

But the way ....the above formula is just confusing ...so am just watching that Youtube video...as per the request from Mr Vidar. Thank You

OK, let me go test those formulas on 1 million rows of data...

OK, on my old gutless machine with 1 million rows of data...

=SUM(IF(FREQUENCY(IF(A2:A1000000<>"",MATCH("~"&A2:A1000000,A2:A1000000&"",0)),ROW(A2:A1000000)-ROW(A2)+1),1))

Average calculation time (5 calculations) = 53.69 secs

=SUM(IF(FREQUENCY(IF(A2:A1000000<>"",MATCH(A2:A1000000,A2:A1000000,0)),ROW(A2:A1000000)-ROW(A2)+1),1))

Average calculation time (5 calculations) = 46.02 secs

The only difference in those formulas is the 1st formula concatenates the tilde and the blank.

There is a 99.999999% chance that you don't need to do that. You only need to do that if your data starts with "special" characters like < , *, >, =, ?.

 

[Sam_D_Ben]

Anyways thanks Mr Valko...I am watching that Youtube video again and again ....its still confusing me. I'll come back once its done ...Thanks again ...Muz