counting entries after data has been sorted

[moskito]

Hi all,

noob to excel macro.. need help! I have managed to sort the data using filter in macro. What I need to do is to make a count of the fields "Owner First Name", "Prod" and "Channel". I would like to use these counters for a automating a chart subsequently. Please see below sample data and codes I have so far.


-- removed inline image ---

Sub SortStatsAsiaTCC()

ActiveSheet.Range("$A$1:$W$8551").AutoFilter Field:=16, Criteria1:= _
"Asia Regional TCC Singapore"
ActiveSheet.Range("$A$1:$W$8551").AutoFilter Field:=21, Criteria1:="AMELIA"

End Sub


'Sub SortOpenCasesByMembers()

' ActiveSheet.Range("$A$1:$W$8551").AutoFilter Field:=21, Criteria1:= _
' "BONIFACIO EDGARDO"
' ActiveSheet.Range("$A$1:$W$8551").AutoFilter Field:=21, Criteria1:="CHIN"
' ActiveSheet.Range("$A$1:$W$8551").AutoFilter Field:=21, Criteria1:="JERLYN"
' ActiveSheet.Range("$A$1:$W$8551").AutoFilter Field:=21, Criteria1:= _
' "JONATHAN"
' ActiveSheet.Range("$A$1:$W$8551").AutoFilter Field:=21, Criteria1:= _
' "MATTHEW"
' ActiveSheet.Range("$A$1:$W$8551").AutoFilter Field:=21, Criteria1:="WENDY"
' ActiveCell.Offset(-6, 8).Range("A1").Select
' ActiveSheet.Range("$A$1:$W$8551").AutoFilter Field:=21
'End Sub
'

Appreciate any help rendered!

 

[moskito]

Hi Greg, this spreadsheet is to be updated on a daily basis, that was why i thought of using macro to automate the counts and plotting of the graphs.
quick question, can pivot table be automated like a macro. pardon me for the dumb questions. i have some sample data already, stripped off the unnecessary fields.

<table border="0" cellpadding="0" cellspacing="0" width="318"><col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:3328;width:68pt" width="91"> <col style="mso-width-source:userset;mso-width-alt:3986;width:82pt" width="109"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt;width:43pt" height="17" width="57">Channel</td> <td class="xl66" style="border-left:none;width:46pt" width="61">Product</td> <td class="xl66" style="border-left:none;width:68pt" width="91">Owner Division</td> <td class="xl66" style="border-left:none;width:82pt" width="109">Owner First Name</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Phone</td> <td>CashPro Online</td> <td>Asia Regional TCC Singapore</td> <td>AMELIA</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Email</td> <td>EDI</td> <td>Asia Regional TCC Singapore</td> <td>AMELIA</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Email</td> <td>CashPro Online</td> <td>Asia Regional TCC Singapore</td> <td>AMELIA</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Email</td> <td>EDI</td> <td>Asia Regional TCC Singapore</td> <td>AMELIA</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Email</td> <td>EDI</td> <td>Asia Regional TCC Singapore</td> <td>AMELIA</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Internal Call</td> <td>CashPro Online</td> <td>Asia Regional TCC Singapore</td> <td>AMELIA</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td>Asia Regional TCC Singapore</td> <td>AMELIA</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Email</td> <td>CashPro Online</td> <td>Asia Regional TCC Singapore</td> <td>AMELIA</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Phone</td> <td>CashPro Online</td> <td>Asia Regional TCC Singapore</td> <td>AMELIA</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Phone</td> <td>EDI</td> <td>Asia Regional TCC Singapore</td> <td>AMELIA</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Phone</td> <td>EDI</td> <td>Asia Regional TCC Singapore</td> <td>AMELIA</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td>Asia Regional TCC Singapore</td> <td>BONIFACIO EDGARDO</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Phone</td> <td>CashPro Online</td> <td>Asia Regional TCC Singapore</td> <td>CHIN</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Phone</td> <td>CashPro Online</td> <td>Asia Regional TCC Singapore</td> <td>CHIN</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td>Asia Regional TCC Singapore</td> <td>CHIN</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Email</td> <td>CashPro Online</td> <td>Asia Regional TCC Singapore</td> <td>CHIN</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td>Asia Regional TCC Singapore</td> <td>JERLYN</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Phone</td> <td>General Request</td> <td>Asia Regional TCC Singapore</td> <td>JONATHAN</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Phone</td> <td>CashPro Online</td> <td>Asia Regional TCC Singapore</td> <td>JONATHAN</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Email</td> <td>CashPro Online</td> <td>Asia Regional TCC Singapore</td> <td>JONATHAN</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Phone</td> <td>
</td> <td>Asia Regional TCC Singapore</td> <td>MATTHEW</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Email</td> <td>EDI</td> <td>Asia Regional TCC Singapore</td> <td>MATTHEW</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Email</td> <td>EDI</td> <td>Asia Regional TCC Singapore</td> <td>MATTHEW</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td>Asia Regional TCC Singapore</td> <td>MATTHEW</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Email</td> <td>CashPro Online</td> <td>Asia Regional TCC Singapore</td> <td>MATTHEW</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Email</td> <td>EDI</td> <td>Asia Regional TCC Singapore</td> <td>MATTHEW</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Phone</td> <td>CashPro Online</td> <td>Asia Regional TCC Singapore</td> <td>WENDY</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Phone</td> <td>CashPro Online</td> <td>Asia Regional TCC Singapore</td> <td>WENDY</td> </tr> </tbody></table>

 

[Greg Truby]

Here is a snapshot of some quick and simple pivot tables and charts I created using the data you posted. Took me maybe three minutes to do once I had the data.



I would strongly urge you to play with pivot tables (and charts) and become familiar with them. They are a critical tool for data analysis. And yes, they are fully manipulatable in VBA. If your data is appended to the same workbook day after day, then all you need to do is use a dynamic named range (or a table[listobject]) as the source. Otherwise you can do a couple of other options like swapping out the data source or building a fresh pivot table each day using VBA.

Pivot are power, fast and flexible. They allow you to slice and dice the data in a variety of ways and they allow quick and easy drilldowns.

Plus, if managers have any kind of Excel skills they can then manipulate the data themselves rather than having to come back to you time-after-time to request new "what-if's" or filters or whatever.