Advanced help! Need to count identical rows and display total, then % of those that difer

[jmay76]

I have to file something with the FCC about my internet customers. I have all my customers listed by FCC areas known as county codes and tract codes, and then the speeds we are delivering to them. I also have them marked as business or residential with a "B" or an "R". What the fcc wants is instead of a line by line item for each customer, they want a single line for each county and tract area with a matching speed to show the total number of connections that exist. So If I have 3 customers in an area with the same speeds I need to consolidate all of them to 1 line and add a column that would show total connections as 3. On top of this I also need to put the percentage of those 3 that are residential. Is there a way to do all this? I have thousands of entries in the main file and need to automate this somehow.

I've included a sample file of what I currently have. The 2nd tab shows how the FCC wants it to look. If anyone can help me I will dedicate my life to becoming a billionaire and buy you the car of your choice!!!

I'm using office 2007 but have access to 2010 also.

[TMS]

That's ideal for a Pivot Table and will take only a few minutes to set up.

Regards, TMS

[jmay76]

Should have joined this forum a week ago! Ok, pivot table broke it down pretty good. But how do I have excel total up the number of connections for each area and put it into another sheet?

[jmay76]

I'm just not getting this. Please help! Seems like this should be simple. Count number of duplicate rows, delete the rows, and give me a total of how many rows there were.