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.

[TABLE="class: grid, width: 723"]
<colgroup><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 2, align: center"]People
[/TD]
[TD="colspan: 6, align: center"]Places
[/TD]
[/TR]
[TR]
[TD]Set A[/TD]
[TD]Set B
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]6
[/TD]
[/TR]
[TR]
[TD]Matt-1[/TD]
[TD]John-1[/TD]
[TD]Philadelphia-1[/TD]
[TD]Philadelphia-1[/TD]
[TD][/TD]
[TD][/TD]
[TD]NYC-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt-1[/TD]
[TD]John-1[/TD]
[TD]NYC-2[/TD]
[TD]NYC-2[/TD]
[TD][/TD]
[TD][/TD]
[TD]NYC-2[/TD]
[TD]NYC-2[/TD]
[/TR]
[TR]
[TD]Matt-1[/TD]
[TD]Dan-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NYC-3[/TD]
[TD]NYC-3[/TD]
[/TR]
[TR]
[TD]John-1[/TD]
[TD]Dan-1[/TD]
[TD]Philadelphia-2[/TD]
[TD]Philadelphia-2[/TD]
[TD]LA-1[/TD]
[TD]Chicago-1[/TD]
[TD]Boston-1[/TD]
[TD]Boston-1
[/TD]
[/TR]
[TR]
[TD]John-1[/TD]
[TD]George-1[/TD]
[TD][/TD]
[TD]Miami-1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Boston-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John-1[/TD]
[TD]George-1[/TD]
[TD][/TD]
[TD]Miami-2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Denver-1[/TD]
[TD]Denver-1[/TD]
[/TR]
[TR]
[TD]Bob-1[/TD]
[TD]Matt-1[/TD]
[TD][/TD]
[TD]Miami-3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Boston-1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

People​

[/TD]
[TD][/TD]
[TD]

Places​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Set​

[/TD]
[TD]

Total​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Set A​

[/TD]
[TD]

Set B​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Set A​

[/TD]
[TD]

13​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Matt-1​

[/TD]
[TD]

John-1​

[/TD]
[TD]

Philadelphia-1​

[/TD]
[TD]

Philadelphia-1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

NYC-1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Set B​

[/TD]
[TD]

14​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Matt-1​

[/TD]
[TD]

John-1​

[/TD]
[TD]

NYC-2​

[/TD]
[TD]

NYC-2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

NYC-2​

[/TD]
[TD]

NYC-2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

Matt-1​

[/TD]
[TD]

Dan-1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

NYC-3​

[/TD]
[TD]

NYC-3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

John-1​

[/TD]
[TD]

Dan-1​

[/TD]
[TD]

Philadelphia-2​

[/TD]
[TD]

Philadelphia-2​

[/TD]
[TD]

LA-1​

[/TD]
[TD]

Chicago-1​

[/TD]
[TD]

Boston-1​

[/TD]
[TD]

Boston-1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

John-1​

[/TD]
[TD]

George-1​

[/TD]
[TD][/TD]
[TD]

Miami-1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Boston-1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

John-1​

[/TD]
[TD]

George-1​

[/TD]
[TD][/TD]
[TD]

Miami-2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Denver-1​

[/TD]
[TD]

Denver-1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

Bob-1​

[/TD]
[TD]

Matt-1​

[/TD]
[TD][/TD]
[TD]

Miami-3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Boston-1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.