Counting company no.s in 1 column based on criteria in another

[Legacy 90606]

working on a very large spreadsheet that has the details of lots of employees and their mode of travel to work.
I need to be able to a countif on the company name based on the criteria in another column

	A	B	C
1	Mode of transport	Company Name
2	Bike	Test Company 1
3	Car	Test Company 2
4	Walk	Test Company 1
5	Car	Test Company 3
6	Bike	Test Company 1
7	Car	Test Company 1
8	Car	Test Company 1
9	Walk	Test Company 2
10	Bike	Test Company 4
11	Car	Test Company 1

<tbody>
</tbody>

I need a table to look at all different permutations and count up the entries for each company like below.

	A	B	C
1	Mode of transport	Companies
2	Bike	1
3	Car	1
4	Walk	0
5	Bike/Car	0
6	Bike/Car/Walk	1
7	Car/Walk	1
8	Bike/Walk	0

<tbody>
</tbody>

 

[MickG]

I will return

 

[MickG]

Based on Data in "A & B and reference column "D" try this for results in column "E".

Mode of transport	Company Name		Mode of transport	Companies
Bike	Test Company 1		Bike	1
Car	Test Company 2		Car	1
Walk	Test Company 1		Walk	0
Car	Test Company 3		Bike/Car	0
Bike	Test Company 1		Bike/Car/Walk	1
Car	Test Company 1		Car/Walk	1
Car	Test Company 1		Bike/Walk	0
Walk	Test Company 2
Bike	Test Company 4
Car	Test Company 1

<colgroup><col width="160" style="width: 120pt; mso-width-source: userset; mso-width-alt: 5688;"> <col width="158" style="width: 118pt; mso-width-source: userset; mso-width-alt: 5603;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3214;"> <col width="159" style="width: 119pt; mso-width-source: userset; mso-width-alt: 5660;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3868;"> <tbody> </tbody>

[COLOR="Navy"]Sub[/COLOR] MG05Sep47
   [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] Num         [COLOR="Navy"]As[/COLOR] Double
    [COLOR="Navy"]Dim[/COLOR] K           [COLOR="Navy"]As[/COLOR] Variant
    [COLOR="Navy"]Dim[/COLOR] p           [COLOR="Navy"]As[/COLOR] Variant
    [COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] Sp          [COLOR="Navy"]As[/COLOR] Variant
    [COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] rNum        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] uNum        [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
           Num = 0
            [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Dn.Offset(, -1).Value
                [COLOR="Navy"]Case[/COLOR] "Bike": Num = 1
                [COLOR="Navy"]Case[/COLOR] "Car": Num = 2
                [COLOR="Navy"]Case[/COLOR] "Walk": Num = 3
            [COLOR="Navy"]End[/COLOR] Select
            
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, -1).Value) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Value).Add (Dn.Offset(, -1).Value), Num
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
  
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
        Dn.Offset(, 1).Value = 0: rNum = 0: Tot = 0
        Sp = Split(Dn.Value, "/")
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
            [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Sp(n)
                [COLOR="Navy"]Case[/COLOR] "Bike": Num = 1
                [COLOR="Navy"]Case[/COLOR] "Car": Num = 2
                [COLOR="Navy"]Case[/COLOR] "Walk": Num = 3
            [COLOR="Navy"]End[/COLOR] Select
        rNum = rNum + Num
        [COLOR="Navy"]Next[/COLOR] n
    
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] Dic.keys
            nStr = "": uNum = 0
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(K)
                uNum = uNum + Dic(K).Item(p)
            [COLOR="Navy"]Next[/COLOR] p
            [COLOR="Navy"]If[/COLOR] uNum = rNum [COLOR="Navy"]Then[/COLOR] Dn.Offset(, 1).Value = Dn.Offset(, 1).Value + 1
       [COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick


www

 

[Legacy 90606]

Thanks Mick I was hoping for some sort of formula as I'm not sure about coding