how to count unique values in an array

[threestacks]

I am trying to write a function that will count all unique values in an array that are associated with a value. For example, I want a function that will:

• go to column 1 (first column all the way to the left)
• If the value in column 1 = Molly, the function will count all unique values in columns 2:7, but only the values from the rows that are associated with Molly's name (here it would be rows 1 and 4)
• In this case, it would count Matt, Russ, David, Dan, Erica, James, Jose, Steve, Chris, and George

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Molly[/TD]
[TD]Matt[/TD]
[TD]Russ[/TD]
[TD]David[/TD]
[TD]Dan[/TD]
[TD]Erica[/TD]
[TD]James[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]Frank[/TD]
[TD]Jen[/TD]
[TD]Liz[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD]Gabi[/TD]
[TD]Andrei[/TD]
[TD]Jorge[/TD]
[TD]Phil[/TD]
[TD]Meghan[/TD]
[TD]Harvey[/TD]
[/TR]
[TR]
[TD]Molly[/TD]
[TD]Jose[/TD]
[TD]Steve[/TD]
[TD]Chris[/TD]
[TD]Dan[/TD]
[TD]George[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jake[/TD]
[TD]Steph[/TD]
[TD]Ellen[/TD]
[TD]Andrew[/TD]
[TD]Nick[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Has anyone done this before that might be able to help? Thanks!

 

[yesterdays]

So you want it to return 10( Matt, Russ, David, Dan, Erica, James, Jose, Steve, Chris, and George)
or 2 ( 2 row contain Molly) ?

 

[Aladin Akyurek]

Let A1:G5 house the sample.

Let I1 house Molly.

I2, control+shift+enter, not just enter:

=SUM(IF(
  FREQUENCY(IF(v(arrayunion(IF($A$1:$A$5=I1,IF($B$1:$G$5<>"",$B$1:$G$5,""),"")))<>"",
  MATCH(v(),v(),0)),ROW(INDIRECT("1:"&COUNTA(v())))),1))

In order to run this formula, install the following functions in VBA in a module, using Alt+F11...

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

 

[threestacks]

I would want it to return 10

 

[Aladin Akyurek]

I would want it to return 10

Post #3 returns 10...

 

[Chris Mack]

I'm no expert at this but thought I'd share what I came up with:

Public Function Counter(cell As Range)
    Application.Volatile
    Dim arr() As String
    arrnum = 0
    n = Cells(Rows.Count, 1).End(xlUp).Row
    For r = 1 To n
        If Cells(r, 1) = cell Then
            For col = 2 To 7
                If Cells(r, col) <> "" Then
                    ReDim Preserve arr(0 To arrnum) As String
                    arr(arrnum) = Cells(r, col)
                    arrnum = arrnum + 1
                End If
            Next
        End If
    Next
    dupcount = 0
    For i = LBound(arr) To UBound(arr)
        iicount = 0
        For ii = LBound(arr) To UBound(arr)
            If arr(ii) = arr(i) And ii < i Then
                iicount = iicount + 1
            End If
        Next ii
        If iicount > 0 Then dupcount = dupcount + 1
    Next i
    Counter = UBound(arr) + 1 - dupcount
End Function

I took some ideas from here:

http://www.mrexcel.com/forum/excel-questions/672885-visual-basic-applications-countif-array.html

Then just...

=Counter(I1)

... in I2.

Chris.