Formula to calculate unique names

[JoakimA]

Hi!

I have a large dataset consisting of column A and B. In column A there is an ID that represents a voyage. In column B I have names on the persons that were on the different voyages. I want to calculate how many unique persons that were on the different voyages. How do I do that?

Below I have shown a small table to give an idea of how it looks.

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Peter[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jacob[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Michael[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jens[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jens[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Trevor[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Peter[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jacob[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Michael[/TD]
[/TR]
</tbody>[/TABLE]

Hope someone can help.
Thank you!

 

[Mazbuka]

There is probably a better way but you can use advanced filter.

Select all rows in the name column, including the heading.

Data---> advanced filter

Action -> copy to another location

List range: this should populate automatically, but it will be B1:Bxxx (assuming NAME column is col B)

Criteria range: copy in the very same as is in list range

Copy to: Select another cell on the same worksheet, say C1

Tick Unique records only then OK & it should do it.

 

[mohadin]

Hi
What about

Sub test()
    Dim a As Variant, i
    a = Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 2)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If a(i, 2) <> 0 Then
                If Not .exists(a(i, 2)) Then
                    .Add a(i, 2), a(i, 1)
                Else
                    .Item(a(i, 2)) = .Item(a(i, 2)) & "," & a(i, 1)
                End If
            End If
        Next
        Cells(1, 1).Resize(, 2).Copy Cells(1, 1).Offset(, 3).Resize(2)
        Cells(1, 1).Offset(1, 3).Resize(.Count, 2) = Application.Transpose(Application.Index(Array(.items, .keys), 0, 0))
    End With
End Sub

 

[James006]

Hello,

Just Insert a Pivot table ...

Hope this will help

 

[JoakimA]

Thanks for the answers.

I tried the code in excel, but could not get it to work, but the pivot table worked perfectly!

 

[James006]

Glad you could fix your problem ...