Using Dictionary objects to handle Excel duplicate values

For Excel duplicate value This problem, more common, Officeba once sent an article: let Excel 2007来 filter unique value or delete duplicate value discussed, today again to use the Dictionary object to handle Excel duplicate values

Write a function to handle the duplicate value of Excel, and then apply it to the instance.

Function MergerRepeat(Index As Integer, ParamArray arglist() As Variant)
'*******************************************
'功能：获得指定单元格区域或数组中的不重复集合或值
'参数说明：
'Index：整型，当值小于1时，函数返回一个集合；
'　　　 大于1且小于不重复项的时，返回一个不重复的值
'　　　 大于不重复项时，返回空。
'arglist()：可为单元格区域或数组常量。
'发布：http://www.officeba.com.cn
'*******************************************
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, the use of this function can return the number of distinct values in a region, taking B1:B10 as an example

Enter a formula in a cell: =counta (Mergerrepeat (0,B1:B10))

Enter that has

2, use this function to return the different cell values are not repeated value comparison

For example, the B1:B10 value of a C1 is listed in column C, starting from the grid.

Enter a formula in the C1: =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))

Although this function is not efficient for processing large amounts of data, it's pretty good. We can write more perfect functions on this basis. Don't forget to share it with us.