Is this even possible? Question on matching counting unique values, but a bit complex

[hoops3335]

Hi,

I have a data set as seen below. I am trying to calculate counting every unique match of entries in the People set A column to cities in the Places section.

For example, strictly looking at Set A; Matt-1 has 4 unique entries (Philadelphia-1, NYC-1,NYC-2,NYC-3). John-1 has 7 unique entries, Bob-1 has 2 unique entries.

The calculation output I am looking for would be 13 (4+7+2).

Similarly, I would like to implement a function to calculate Set B and it's unique matches.

Is this possible to accomplish? Please note this equation would not include/count blanks.

People	Places
Set A	Set B	1	2	3	4	5	6
Matt-1	John-1	Philadelphia-1	Philadelphia-1			NYC-1
Matt-1	John-1	NYC-2	NYC-2			NYC-2	NYC-2
Matt-1	Dan-1					NYC-3	NYC-3
John-1	Dan-1	Philadelphia-2	Philadelphia-2	LA-1	Chicago-1	Boston-1	Boston-1
John-1	George-1		Miami-1			Boston-1
John-1	George-1		Miami-2			Denver-1	Denver-1
Bob-1	Matt-1		Miami-3			Boston-1

<colgroup><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe a UDF (User Defined Function) does the job

Alt+F11 to open the VB Editor
Menu
Insert > Module

Paste the code below in the right panel

Function TotalUnique(rngSet As Range, rngPlaces As Range)
    Dim dict As Object, i As Long, r1 As Range
    
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    For i = 1 To rngSet.Rows.Count
        For Each r1 In rngPlaces.Rows(i).Cells
            If r1 <> "" Then dict(rngSet.Cells(i, 1).Value & "|" & r1.Value) = Empty
        Next r1
    Next i
    
    TotalUnique = dict.Count
End Function

Back to Excel

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	People​		Places​							Set​	Total​
2​	Set A​	Set B​								Set A​	13​
3​	Matt-1​	John-1​	Philadelphia-1​	Philadelphia-1​			NYC-1​			Set B​	14​
4​	Matt-1​	John-1​	NYC-2​	NYC-2​			NYC-2​	NYC-2​
5​	Matt-1​	Dan-1​					NYC-3​	NYC-3​
6​	John-1​	Dan-1​	Philadelphia-2​	Philadelphia-2​	LA-1​	Chicago-1​	Boston-1​	Boston-1​
7​	John-1​	George-1​		Miami-1​			Boston-1​
8​	John-1​	George-1​		Miami-2​			Denver-1​	Denver-1​
9​	Bob-1​	Matt-1​		Miami-3​			Boston-1​
10​

<tbody>
</tbody>

Usage
The function requires two range arguments; the first is the range containing the People of each set. the second is the range containing the Places

So, insert these formulas
K2
=TotalUnique(A3:A9;C3:H9)

K3
=TotalUnique(B3:B9;C3:H9)

Hope this helps

M.

 

[hoops3335]

Marcelo,

That is fantastic, and you are amazing. Thank you so much!!!!! I sincerely appreciate your help.

One other question.. How do I execute a unique count say (C3:H9), but not include the blanks. I have tried to =sum(1/(countif(C3:H9,C3:H9))) Ctrl+Shift+Enter, but this only works if there are not blanks. Do you have an idea of how to implement a function which would simply count unique entries in a given range, but not include blanks.

For example C3:H9 output would be 12 since there are 12 unique entries in that range.

 

[Marcelo Branco]

Try this array formula
=SUM(IF(C3:H9<>"",1/COUNTIF(C3:H9,C3:H9)))
confirmed with Ctrl+Shift+Enter, not just Enter

M.