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>

First Name	Last Name	Role	Start Up
George	Aquino	Invested	Initech
George	Aquino	Invested	Acme Corporation
Alban	Dushku	Founded	Massive Dynamic
Alban	Dushku	Exited	Massive Dynamic
Femi	Folami	Invested	Soylent Corp
Femi	Folami	Invested	Soylent Corp

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="175"></colgroup><tbody>
</tbody>

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>

First Name	Last Name	Acquired	Exited	Founded	Invested
George	Aquino				2
Alban	Dushku		1	1
Femi	Folami				1

<colgroup><col style="width: 100px"><col width="100"><col width="135"><col width="135"><col width="135"><col width="135"></colgroup><tbody>
</tbody>

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>

First Name	Last Name	Role	Start Up
George	Aquino	Invested	Initech
George	Aquino	Invested	Acme Corporation
Alban	Dushku	Founded	Massive Dynamic
Alban	Dushku	Exited	Massive Dynamic
Femi	Folami	Invested	Soylent Corp
Femi	Folami	Invested	Soylent Corp

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="175"></colgroup><tbody>
</tbody>

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