Totaling Heat Types by Towns

[mkeohan]

Hello Everyone,

I have a large list of addresses that have heat system types associated with them. I want to be able to get it into a format where it is just the town name and a total number of each heat type. I would normally do this manually but there are over 1 million addresses in this file. This forum wont let me post attachments for whatever reason so I have copied it below.

			MA	02351		2308		745
			MA	02351		2308		844
			MA	02351		2307		719
			MA	02351		1526		267
			MA	02351		1526		366
			MA	02351		1525		475
			MA	02351		2229		374
			MA	02351		2229		338
			MA	02351		2514		142
			Pond St		MA	02322	02322-1624	1624		431
			Pond St		MA	02322	02322-1624	1624		297
			Pond St		MA	02322	02322-1624	1624		954
			KING ST		MA	02322	02322-1205	1205		058
			MA	02322	02322-1244	1244		064
			MA	02322	02322-1244	1244		082

<colgroup><col style="width:48pt" width="64" span="5"> <col style="width:48pt" width="64"> <col style="width:48pt" width="64" span="6"> <col style="width:48pt" width="64"> </colgroup><tbody>
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 64"]Town[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]HEAT SYS[/TD]

[TD="colspan: 2"]CONSTITUTION AVE[/TD]
[TD="class: xl63"]ABINGTON[/TD]

[TD="class: xl63"]ELC[/TD]

[TD="colspan: 2"]CONSTITUTION AVE[/TD]
[TD="class: xl63"]ABINGTON[/TD]

[TD="class: xl63"]ELC[/TD]

[TD="colspan: 2"]CONSTITUTION AVE[/TD]
[TD="class: xl63"]ABINGTON[/TD]

[TD="class: xl63"]ELC[/TD]

[TD="colspan: 2"]Jennings Dr[/TD]
[TD="class: xl63"]Abington[/TD]

[TD="class: xl63"]Gas[/TD]

[TD="colspan: 2"]Jennings Dr[/TD]
[TD="class: xl63"]Abington[/TD]

[TD="class: xl63"]Gas[/TD]

[TD="colspan: 2"]Jennings Dr[/TD]
[TD="class: xl63"]Abington[/TD]

[TD="class: xl63"]Gas[/TD]

[TD="colspan: 2"]CENTRE AVE[/TD]
[TD="class: xl63"]ABINGTON[/TD]

[TD="class: xl63"]Oil[/TD]

[TD="colspan: 2"]CENTRE AVE[/TD]
[TD="class: xl63"]ABINGTON[/TD]

[TD="class: xl63"]Oil[/TD]

[TD="colspan: 2"]CHERRY ST[/TD]
[TD="class: xl63"]ABINGTON[/TD]

[TD="class: xl63"]Oil[/TD]

[TD="class: xl63"]Avon[/TD]

[TD="class: xl63"]Gas[/TD]

[TD="class: xl63"]Avon[/TD]

[TD="class: xl63"]Gas[/TD]

[TD="class: xl63"]Avon[/TD]

[TD="class: xl63"]Gas[/TD]

[TD="class: xl63"]AVON[/TD]

[TD="class: xl63"]Oil[/TD]

[TD="colspan: 2"]BLANCHARD ST[/TD]
[TD="class: xl63"]AVON[/TD]

[TD="class: xl63"]Oil[/TD]

[TD="colspan: 2"]BLANCHARD ST
[/TD]
[TD="class: xl63"]AVON[/TD]

[TD="class: xl63"]Oil
[/TD]

</tbody>

The desired result would be:

Abington
Avon

<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>
[TD="width: 64"][/TD]
[TD="width: 64"]ELEC[/TD]
[TD="width: 64"]GAS[/TD]
[TD="width: 64"]OIL[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3
[/TD]

</tbody>

The two columns in questions are F=Towns and M=Heat Type

Please let me know if any of you can help. Thanks!

 

[Joe4]

I was working off the assumption that in your summary table, you have the three heat types across the top (CF1:CH1) , and list of the cities down the left-side column (starting in CE2 and going down).
If you do not have that unique list of cities, one easy way to get is to copy the values from column F over to column CE, and use the "Remove Duplicates" features.