Need help in counting

[rainx]

Sample Data: I have tis set of sample data that I have already managed to get the names counted in each class using vba code as shown below. Now I need to count those names that belongs to the singapore branch. I have done a match with my namelist using MATCH in column E, if return a number, it means a match wich means the person belongs to singapore brach. So now I need to count those names who belongs to SIngapore branch in each class. Please help thanks!!

Sub Count()
Dim oneCell As Range
With ThisWorkbook.Sheets("sheet1").Range("A:A")
    With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        .Offset(.Rows.Count, 0).Range("A1:A2") = [{"x";"---"}]
        With .Offset(0, 1)
            .Cells(1, 1).FormulaArray = _
                  "=IF((LEN(SUBSTITUTE(R[1]C1,""-"",""""))<(LEN(R[1]C1)-1)),SMALL(IF(LEN(RC1:R[99]C1)-LEN(SUBSTITUTE(RC1:R[99]C1,""-"",""""))>1,ROW(RC1:R[99]C1),9999),2) - SMALL(IF(LEN(RC1:R[99]C1)-LEN(SUBSTITUTE(RC1:R[99]C1,""-"",""""))>1,ROW(RC1:R[99]C1),9999),1)-3,"""")"
            .FillDown
            .Value = .Value
        End With
        .Offset(.Rows.Count, 0).Range("A1:A2").ClearContents
        On Error Resume Next
        For Each oneCell In .SpecialCells(xlCellTypeBlanks)
            If oneCell.Offset(-3, 1).Value Then oneCell.Offset(-3, 1).Value = 0
        Next oneCell
        With .Offset(0, 1).Cells(.Rows.Count, 1).End(xlUp)
            If .Value < 0 Then .Value = 0
        End With
        On Error GoTo 0
    End With
End With
End Sub

<table x:str="" style="border-collapse: collapse; width: 492pt;" width="655" border="0" cellpadding="0" cellspacing="0"><col style="width: 259pt;" width="345"> <col style="width: 69pt;" width="92"> <col style="width: 62pt;" width="83"> <col style="width: 49pt;" width="65"> <col style="width: 53pt;" width="70"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl28" style="height: 12.75pt; width: 259pt;" width="345" height="17">A</td> <td class="xl28" style="width: 69pt;" width="92">B</td> <td class="xl28" style="width: 62pt;" width="83">C</td> <td class="xl28" style="width: 49pt;" width="65">D</td> <td class="xl28" style="width: 53pt;" width="70">E</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 259pt;" width="345" height="17">Class 1</td> <td class="xl24" style="border-left: medium none; width: 69pt;" width="92">Instructor: </td> <td class="xl24" style="border-left: medium none; width: 62pt;" x:str="Tom " width="83">Tom </td> <td class="xl24" style="border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" x:str="02/11/2008 - 02/13/2008 8:30 AM - 5:00 PM " width="345" height="17">02/11/2008 - 02/13/2008 8:30 AM - 5:00 PM </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">Room:</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83">Singapore</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Calvin</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">57725</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">tom</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">59085</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">mary</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">1486</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">david</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">1605</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">James</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">1658</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Phillip</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">2911</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Grace</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60567</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Kiro</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">CFN</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60202</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Adam</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">58876</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Rain</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60042</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Class 2</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">Instructor: </td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83">Ryan</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" x:str="04/14/2008 - 04/16/2008 8:30 AM - 5:00 PM " width="345" height="17">04/14/2008 - 04/16/2008 8:30 AM - 5:00 PM </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">Room:</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" x:str="Singapore " width="83">Singapore </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Calvin</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">21385</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">31</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">tom</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">57643</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">38</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">mary</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">58949</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">55</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">david</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">57547</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">58</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">James</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">57565</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">66</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Phillip</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">58477</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">72</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Grace</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">58149</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">75</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Kiro</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60859</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">82</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Adam</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">20248</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">87</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Rain</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60902</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Rachel</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60169</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">108</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Paulyn</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">59656</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">118</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">adeline</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60810</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">122</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">catherine</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">59249</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">126</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">charles</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60133</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">128</td> </tr> </tbody></table>

 

[jindon]

How do you know who is in or not in a Singapore class out of your sample data ?

 

[rainx]

If column E return a number, it means it matches with my namelist, my namelist only contain those frm Singapore branch, actually I have no use for the position number it return frm the match formula, I simply use it just to identify if dey are frm Singapore. those that reutrn error or dun contain anything, it means they could not be found in the namelist.

Thanks!

 

[jindon]

Then simply ?

Sub test()
Dim x As Long
With Columns("e")
    On Error Resume Next
    x = .SpecialCells(-4123, 1).Count
    MsgBox IIf(Err = 0, x, 0)
End With
End Sub

 

[rainx]

But I want to count those that belongs to each class. which means like class 1 how many from singapore, class 2 how many, and so on and so for.. Yup...cos I have many many classes. In the data above, there are 3 classes.

Thanks!

 

[jindon]

Your asmple data seems Class1 has nothing and Class2 has all.
Is this always happenening or you could have some numbers in Class1 sometime ?

 

[rainx]

Hmm, there are cases where class 1 has those frm SIngapore too.. There are many many classes with different class names, so these are jus few classes I picked up randomly as sample data. Yup...

THanks!

 

[rainx]

actually if you can help me change the code above that is counting the names in each class, I believe it will be able to apply to column E too, cos I tried changing but cant relli make it work...

Thanks alot!

 

[jindon]

Sorry, my method will be different from your? code.
If you just want to edit your code, then you need to wait someone else to comes in.
Bye...

 

[rainx]

I am open to other solutions too but thought I could give some suggestion.. sorry..

thanks anyway..