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.

					Town							HEAT SYS
			CONSTITUTION AVE	ABINGTON	MA	02351		2308		745	ELC
			CONSTITUTION AVE	ABINGTON	MA	02351		2308		844	ELC
			CONSTITUTION AVE	ABINGTON	MA	02351		2307		719	ELC
			Jennings Dr	Abington	MA	02351		1526		267	Gas
			Jennings Dr	Abington	MA	02351		1526		366	Gas
			Jennings Dr	Abington	MA	02351		1525		475	Gas
			CENTRE AVE	ABINGTON	MA	02351		2229		374	Oil
			CENTRE AVE	ABINGTON	MA	02351		2229		338	Oil
			CHERRY ST	ABINGTON	MA	02351		2514		142	Oil
			Pond St		Avon	MA	02322	02322-1624	1624		431	Gas
			Pond St		Avon	MA	02322	02322-1624	1624		297	Gas
			Pond St		Avon	MA	02322	02322-1624	1624		954	Gas
			KING ST		AVON	MA	02322	02322-1205	1205		058	Oil
			BLANCHARD ST	AVON	MA	02322	02322-1244	1244		064	Oil
			BLANCHARD ST	AVON	MA	02322	02322-1244	1244		082	Oil

<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>
</tbody>

The desired result would be:

	ELEC	GAS	OIL
Abington	3	3	3
Avon		3	3

<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>
</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.