2016 Michigan Teams by County

[Ed Law]

Originally Posted by Michael Kaurich

Is there a way you can share sheet for other regions to use? I would love to do the same thing with Maryland

It is something we have in spreadsheet forms but have never really put into a map.

There is no magic spreadsheet that you can program to create that map. It is just hours of hard work. Here is basically what you need to do.
1) You need a list of all zip codes in your area of interest (state/district/region)
2) You need the corresponding city for each of the zip codes. Some have more than one zip code.
3) You need a table to show which county each city belongs to.
4) Then you get all the teams in your area of interest.
5) Use vlookup function in Excel to find the county for each team.
6) Use pivot table to tabulate how many teams in each county.
7) Find a blank map that shows the counties in your area of interest.
8) Find a map with the name of each county.
9) Then manually create a text box for each county, put it inside the county on the blank map and link the value from the pivot data to it. This is the time consuming and tedious part.

The good thing is once you create the link. Every time you update the team lists in the future, you only have to refresh the pivot table and you will have a new map. Michigan used to create this map manually in the past by counting. Good thing we are all just crazy volunteers.

I did one for California too which is my new home.