about how to extract custom functions that Excel does not duplicate values

about how to extract a custom function that does not duplicate a value in Excel, extract the topic of repeated values, in Excel is a very common topic, this site also collected a few on the formula to solve several methods: in this to achieve the effect of a discussion, if there is wrong, I hope everyone corrected, I good in time to modify! Let me explain in detail:

The complete code is as follows:

Function MergerRepeat(Index As Integer, ParamArray arglist() As Variant)
---------------------------------------------
'时间：2008-12-26
'理整：宇 bengdeng
'功能：获得指定单元格区域或数组中的不重复集合或值
'参数说明：
'Index：整型，当值小于1时，函数返回一个集合；
'　　　 大于1且小于不重复项的时，返回一个不重复的值
'　　　 大于不重复项时，返回空。
'arglist()：可为单元格区域或数组常量。
----------------------------------------------
Dim NotRepeat As Object, tStr As String
Set NotRepeat = CreateObject("Scripting.Dictionary")
For Each arg In arglist
For Each rRan In arg
If TypeName(rRan) = "Range" Then
If rRan.Value <> "" Then NotRepeat(rRan.Value) = 0
Else
NotRepeat(rRan) = 0
End If
Next
Next
If Index < 1 Then
MergerRepeat = NotRepeat.keys
ElseIf Index <= NotRepeat.Count Then
arr = NotRepeat.keys
MergerRepeat = arr(Index - 1)
Else
MergerRepeat = ""
End If
End Function

Here are some examples of applications to illustrate the application of this function.

1, return the number of A1:A10 values.

=counta (Mergerrepeat (0,A1:A10))

2. List the a1:a10 values of the B1 in column B from the beginning of the grid.

Set the formula in B1:

=mergerrepeat (ROW (), $A $: $A $)

and fill it down.

3, to find multiple areas (can not be continuous) Addends group of the number of repetitions.

=counta (Mergerrepeat (0,a1:a6,{"abc", "Excel Bar", 1, "excelba.com"},c2:c6))

The above has been fully elaborated on the use of this method, we can combine the practice to verify my ideas, if a friend thinks good, reprinted, please indicate your article from officeba.com.cn, thank you, I will provide more and better articles to everyone!