Count unique cells after multi-cell matching criteria against a key cell

[brashandcrass]

Hopefully I can articulate what it is I'm trying to get: I want to count how many different START UP(S) a PERSON has had specific ROLES in.


In other words, where ever (A2&B2) reoccur in A:B, count how many times C2 occurs (next to the adjacent A & B cells) next to UNIQUE values in the adjacent cell in $D.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="175"></colgroup><tbody>[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Role[/TD]
[TD]Start Up[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Aquino[/TD]
[TD]Invested[/TD]
[TD]Initech[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Aquino[/TD]
[TD]Invested[/TD]
[TD]Acme Corporation[/TD]
[/TR]
[TR]
[TD]Alban[/TD]
[TD]Dushku[/TD]
[TD]Founded[/TD]
[TD]Massive Dynamic[/TD]
[/TR]
[TR]
[TD]Alban[/TD]
[TD]Dushku[/TD]
[TD]Exited[/TD]
[TD]Massive Dynamic[/TD]
[/TR]
[TR]
[TD]Femi[/TD]
[TD]Folami[/TD]
[TD]Invested[/TD]
[TD]Soylent Corp[/TD]
[/TR]
[TR]
[TD]Femi[/TD]
[TD]Folami[/TD]
[TD]Invested[/TD]
[TD]Soylent Corp[/TD]
[/TR]
</tbody>[/TABLE]

The correct answers are:

• George:Aquino:Invested [in] X unique start up(s)... 2
• Alban:Dushku:Founded X unique start up(s)... 1
• Alban:Dushku:Exited X unique start up(s)... 1
• Femi:Folani:Invested [in] X unique start up(s)... 1

I want to end up with a tab like this:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="135"><col width="135"><col width="135"><col width="135"></colgroup><tbody>[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD="align: center"]Acquired[/TD]
[TD="align: center"]Exited[/TD]
[TD="align: center"]Founded[/TD]
[TD="align: center"]Invested[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Aquino[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]Alban[/TD]
[TD]Dushku[/TD]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Femi[/TD]
[TD]Folami[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]



Thanks!

 

[James006]

Hello,

This is an Aladin question ...!!!

Say for the "Invested" formula, you could test the following Array Formula:

=SUM(IF(FREQUENCY(IF($A$2:$A$7&$B$2:$B$7=A2&B2,IF($C$2:$C$7="Invested",MATCH($D$2:$D$7,$D$2:$D$7,0))),ROW($A$2:$A$7)-ROW($A$2)+1),1))

Hope this will help

 

[brashandcrass]

James006,

It takes genius to even start to formulate this equation I was trying to describe. Thanks for giving it a go. Unfortunately, this formula is not working. Even in this small demo set, it's returning George:Aquino:Invested [in] X unique start up(s)... 1 when the answer is 2.

Did you happen to have another approach in mind? I wish I could help but I'm under-gunned for this challenge.

Thanks again. Would love to see you take another crack at it!

 

[James006]

Hello again,

The formula is an Array Formula ...

So instead of the standard Enter key ... you need to simultaneously use the three keys : Control Shift Enter ...

Hope this clarifies

 

[MickG]

Try this for results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG22Sep19
[COLOR="Navy"]Dim[/COLOR] Hds         [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Q, n        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Txt         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Hds = Array("First Name", "Last Name", "Acquired", "Exited", "Founded", "Invested")
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   n = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
         Txt = Dn & ", " & Dn.Offset(, 1)
        [COLOR="Navy"]If[/COLOR] Not Dic.exists(Txt) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Dic(Txt) = CreateObject("Scripting.Dictionary")
            n = n + 1
         [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]If[/COLOR] Not Dic(Txt).exists(Dn.Offset(, 2).Value) [COLOR="Navy"]Then[/COLOR]
            Dic(Txt).Add (Dn.Offset(, 2).Value), Array(n, 1)
        [COLOR="Navy"]Else[/COLOR]
            Q = Dic(Txt).Item(Dn.Offset(, 2).Value)
                Q(1) = Q(1) + 1
            Dic(Txt).Item(Dn.Offset(, 2).Value) = Q
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
   
   
   [COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
   ReDim ray(1 To Dic.Count + 1, 1 To UBound(Hds) + 1)
   n = 0
   [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Hds): ray(1, n + 1) = Hds(n): [COLOR="Navy"]Next[/COLOR] n
    
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
              Col = Application.Match(p, Hds, 0)
                ray(Dic(k).Item(p)(0), 1) = Split(k, ",")(0)
                ray(Dic(k).Item(p)(0), 2) = Split(k, ",")(1)
                ray(Dic(k).Item(p)(0), Col) = Dic(k).Item(p)(1)
            [COLOR="Navy"]Next[/COLOR] p
    [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(UBound(ray, 1), UBound(ray, 2))
       .Value = ray
       .Borders.Weight = 2
       .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[brashandcrass]

I feel foolish. The ol' "CSE"... it worked of course. Thanks, James006!

 

[James006]

I feel foolish. The ol' "CSE"... it worked of course. Thanks, James006!

Absolutely no problem ...

Glad you could fix your problem ...:wink:

 

[brashandcrass]

James006,

Can I ask you for a variant of this formula where we use the original matrix:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="175"></colgroup><tbody>[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Role[/TD]
[TD]Start Up[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Aquino[/TD]
[TD]Invested[/TD]
[TD]Initech[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Aquino[/TD]
[TD]Invested[/TD]
[TD]Acme Corporation[/TD]
[/TR]
[TR]
[TD]Alban[/TD]
[TD]Dushku[/TD]
[TD]Founded[/TD]
[TD]Massive Dynamic[/TD]
[/TR]
[TR]
[TD]Alban[/TD]
[TD]Dushku[/TD]
[TD]Exited[/TD]
[TD]Massive Dynamic[/TD]
[/TR]
[TR]
[TD]Femi[/TD]
[TD]Folami[/TD]
[TD]Invested[/TD]
[TD]Soylent Corp[/TD]
[/TR]
[TR]
[TD]Femi[/TD]
[TD]Folami[/TD]
[TD]Invested[/TD]
[TD]Soylent Corp[/TD]
[/TR]
</tbody>[/TABLE]

and pull a list of unique names (where a "name" = ColA + ColB)?

Thanks again.

 

[James006]

For the sake of clarity ... answer is available at the following thread :

https://www.mrexcel.com/forum/excel...ique-values-when-adjacent-cells-combined.html