Counting Unique Values across multiple rows

[fb7894]

I have a real head scratcher here. I need to count unique values in multiple columns which can span mulitple rows.

The attachment explains it pretty well. I'm trying to write a formula for column F. I need to count the unique people at the deal-id level. I think a monster array formula would do it, but i cannot figure it out.

Thanks in advance.

[SamT]

There is probably an array formula that would do it, but I'm not a formula guy.

I would use a Collection.Add which gives a error if you add an existing Key.

Loop thru column A defining the Range B:E Then
For each Cel in Range
On Error Resume Next
Collection.Add("X", Cel) 'Where "X" is the Item place holder
Error = 0
Next Cel

If you wanted a list and count of unique names
Collection.Add(Cel, Cel)

Number of unique names = Collection.Count

[Kenneth Hobs]

In a Module, use as a Sub, or as a UDF.

Sub Test_CountUniqueByRow()
MsgBox CountUniqueByRow(Range("A2:E6"), Range("A2").Value)
End Sub

' =CountUniqueByRow($A$2:E10,A6)
Function CountUniqueByRow(aRange As Range, id As Variant) As Long
Dim col As New Collection
Dim c As Range, r As Range
Dim i As Long

Application.Volatile False

On Error Resume Next
For Each r In aRange.Rows
For Each c In r.Cells
If r.Cells(1, 1).Value = id Then col.Add c, c
Next c
Next r
If col.Count > 0 Then i = col.Count - 1
Set col = Nothing
CountUniqueByRow = i
End Function