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

 

[Vidar]

Hi
Try this:
=SUM(IF(FREQUENCY(IF(A2:A65000<>"",MATCH("~"&A2:A65000,A2:A65000&"",0)),ROW(A2:A65000)-ROW(A2)+1),1))
Didn't use the whole column A because it would also count the header in the count. Increase the rows in the formula if 65000 isn't enough.

Vidar

 

[T. Valko]

Excel 2007

	A
Products

<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Name1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Name1[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Name1[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Name2[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Name2[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Name2[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]Name3[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]Name3[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]Name3[/TD]

</TBODY>

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

Create a dynamic range...

Formulas tab>Defined Names>Define Name
Name: Names (or whatever name you want to use)
Refers to:

=$A$2:INDEX($A:$A,MATCH("zzzzz",$A:$A))

OK

Then, this array formula** to get the count of uniques:

=SUM(IF(FREQUENCY(IF(Names<>"",MATCH(Names,Names,0)),ROW(Names)-MIN(ROW(Names))+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[AgentSmith]

If speed is essential then you may be able to use SUMPRODUCT:

=SUMPRODUCT(--(A2:A14<>""),--(A2:A14<>A3:A15))

But this only works if your data obeys some rules:

1) Names must appear grouped together in blocks, e.g.
Name 1
Name 2
Name 1
... is not allowed

2) There can be no blanks between names that are the same, e.g.
Name 1

Name 1
... is also not allowed.

If you need greater flexibility than this then go for one of the other suggestions.

 

[Vidar]

Hi
Try this:
=SUM(IF(FREQUENCY(IF(A2:A65000<>"",MATCH("~"&A2:A65000,A2:A65000&"",0)),ROW(A2:A65000)-ROW(A2)+1),1))
Didn't use the whole column A because it would also count the header in the count. Increase the rows in the formula if 65000 isn't enough.

Vidar

Forgot to mention that it's an array formula that needs to be entered with Ctrl+Shift+Enter.
Vidar

 

[Sam_D_Ben]

Yeah am back. All this time i was working with my data using all your inputs. Mr Vidar special thanks to you. Yours works fast even in 10-lakh data's. I'll get the unique count in seconds. Anyways T.Valko & AgentSmith thanks to you too for your support. Btw Mr.Vidar Could you please explain or evaluate your formula. I am curious

 

[Vidar]

Yeah am back. All this time i was working with my data using all your inputs. Mr Vidar special thanks to you. Yours works fast even in 10-lakh data's. I'll get the unique count in seconds. Anyways T.Valko & AgentSmith thanks to you too for your support. Btw Mr.Vidar Could you please explain or evaluate your formula. I am curious

Hi Muzama
I wish the construct of the formula was mine, but it's not. It's been made and improved by people in here.
It takes forever to write an explanation for it in here. I suggest you take a look at this detailed and informative
video from ExcelIsFun on how it's built from the ground up. For the particular formula i suggested to you, jump to 27:23.
But I can recommend to watch the whole video to fully understand it.
Excel Magic Trick 627: FREQUENCY Array Function (10 Examples) - YouTube#

Vidar

 

[Sam_D_Ben]

Sure I'll go watch it. Cheers Vid

 

[T. Valko]

Yeah am back. All this time i was working with my data using all your inputs. Mr Vidar special thanks to you. Yours works fast even in 10-lakh data's. I'll get the unique count in seconds. Anyways T.Valko & AgentSmith thanks to you too for your support. Btw Mr.Vidar Could you please explain or evaluate your formula. I am curious

I'm curious. How much is 10-lakh data's?

You said that data gets added daily so I suggested using a dynamic range which will be more efficient than just using a very large range.

In my tests the formula I suggested is almost twice as fast to caluculate on a static range of 65,000 rows.

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

Average calculation time (5 calculations) = 1.49 secs

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

Average calculation time (5 calculations) = 0.82 secs

 

[Mindpsyche]

Hi Biff,

Lakh is used primarily on the Indian Sub continent.

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