Count Total, Unique, array formula?

[Pohguan]

Hi, I have a table below which i need to count the number of unique people who own the same brand of car as Peter. The formula should return 3, 2 from toyota(Paul and David) and 1 from Honda(Ken), it should count David from Nissan as it's a duplicate. Anyone can help to solve this? Thanks

Car​	Year​	Name​
Toyota	1999	Peter
Toyota	2000	Paul
Toyota	1998	David
Mazda	1997	Michael
Honda	1997	Peter
Honda	1998	Ken
Nissan	1999	Peter
Nissan	1998	David
Ford	1999	Peter

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F
1	Car	Year	Name		Name	Count
2	Toyota	1999	Peter		Peter	3
3	Toyota	2000	Paul
4	Toyota	1998	David
5	Mazda	1997	Michael
6	Honda	1997	Peter
7	Honda	1998	Ken
8	Nissan	1999	Peter
9	Nissan	1998	David
10	Ford	1999	Peter
Sheet7

Cell Formulas
Range	Formula
F2	F2	{=SUM(SIGN(FREQUENCY(IF(COUNTIFS(A2:A10,A2:A10,C2:C10,E2),MATCH(C2:C10,C2:C10,0)),ROW(C2:C10)-ROW(C2)+1)))-1}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Pohguan]

Thanks, however my actual listing contains over 50,000 rows and about 30 columns, this array formula will take too long to run. is it possible to use vba by any chance if i just need to show autofilter of the 3 "resulted rows"?

 

[Peter_SSs]

Welcome to the MrExcel board!

You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.

Function CountSame(data As Range, owner As String) As Long
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = data.Value
  owner = LCase(owner)
  For i = 1 To UBound(a)
    a(i, 3) = LCase(a(i, 3))
    If a(i, 3) = owner Then d(a(i, 1)) = Empty
  Next i
  For i = 1 To UBound(a)
    If a(i, 3) <> owner Then
      If d.exists(a(i, 1)) Then
        If Not d.exists("##" & a(i, 3)) Then
          CountSame = CountSame + 1
          d("##" & a(i, 3)) = Empty
        End If
      End If
    End If
  Next i
End Function

Book1
	A	B	C	D	E	F
1	Car	Year	Name		Name	Count
2	Toyota	1999	Peter		Peter	3
3	Toyota	2000	Paul		Paul	2
4	Toyota	1998	David		Ken	1
5	Mazda	1997	Michael
6	Honda	1997	Peter
7	Honda	1998	Ken
8	Nissan	1999	Peter
9	Nissan	1998	David
10	Ford	1999	Peter
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=CountSame(A$1:C$10,E2)