Does anyone have CT/RI CD results broken down by county, rather than town?

[Calthrina950]

No, it is not running in Protected Mode, and I merely copied and pasted the relevant parts of the Daily Kos spreadsheet onto a Excel spreadsheet, as jimrtex had earlier specified. And I've checked Options, and the boxes which you indicate are unchecked.

Is the Daily Kos spreadsheet still open in another window? That sometimes will affect the other windows. In my experience, having protected mode on in one Excel Workbook has a tendency to slow down and sometimes screw up other workbooks. It's probably best to close the Daily Kos spreadsheet, if it's still open.

The Daily Kos spreadsheet is not open at all. As I said, I just copied and pasted it into another workbook, and closed the original spreadsheet soon thereafter. I'm trying to determine the results by county for Connecticut's congressional districts (along with the total vote cast by each county within each district), similar to how you determined Alaska's presidential election results by county.

[cinyc]

No, it is not running in Protected Mode, and I merely copied and pasted the relevant parts of the Daily Kos spreadsheet onto a Excel spreadsheet, as jimrtex had earlier specified. And I've checked Options, and the boxes which you indicate are unchecked.

Is the Daily Kos spreadsheet still open in another window? That sometimes will affect the other windows. In my experience, having protected mode on in one Excel Workbook has a tendency to slow down and sometimes screw up other workbooks. It's probably best to close the Daily Kos spreadsheet, if it's still open.

The Daily Kos spreadsheet is not open at all. As I said, I just copied and pasted it into another workbook, and closed the original spreadsheet soon thereafter. I'm trying to determine the results by county for Connecticut's congressional districts (along with the total vote cast by each county within each district), similar to how you determined Alaska's presidential election results by county.

I understand what you're trying to do. I'm trying to troubleshoot why your Find and Replace CTRL-H function isn't working. In theory, it should work. I'm stumped now.

[Calthrina950]

No, it is not running in Protected Mode, and I merely copied and pasted the relevant parts of the Daily Kos spreadsheet onto a Excel spreadsheet, as jimrtex had earlier specified. And I've checked Options, and the boxes which you indicate are unchecked.

Is the Daily Kos spreadsheet still open in another window? That sometimes will affect the other windows. In my experience, having protected mode on in one Excel Workbook has a tendency to slow down and sometimes screw up other workbooks. It's probably best to close the Daily Kos spreadsheet, if it's still open.

The Daily Kos spreadsheet is not open at all. As I said, I just copied and pasted it into another workbook, and closed the original spreadsheet soon thereafter. I'm trying to determine the results by county for Connecticut's congressional districts (along with the total vote cast by each county within each district), similar to how you determined Alaska's presidential election results by county.

I understand what you're trying to do. I'm trying to troubleshoot why your Find and Replace CTRL-H function isn't working. In theory, it should work. I'm stumped now.

I'm not sure what's going on either. Maybe jimrtex will have some additional insights later.

[jimrtex]

I've checked, and the "Match entire cell contents" box is unchecked. However, I'm confused about what you outline to do after that-I'm still not too familiar with how the "Replace All" option works.

Was it checked, and then you unchecked it?

If it was checked, then when you searched on " (pt.)" it would only find cells that consisted entirely of " (pt.)". Since there were no such cells, it was returning a warning message. Unchecking it should let it find the cell "Glastonbury (pt.)" and replace the " (pt.)" with nothing.

If you can select Column C, then Ctrl-H, and enter the find string of " (pt.)" and the replace string of nothing, when you press Replace All does it make 10 replacements (or 9 if you have changed "Glastonbury (pt.)" by hand?

If so you can go to the next step.

OR, you can press Ctrl-Z to undo the replacement. And then with Column C selected, then Ctrl-H, press Find Next and it will highlight "Glastonbury (pt.)" or "Middletown (pt.). Pressing Replace will make the replacement in that cell only, and then advance to the next cell containing the search string.

If you had some reason for not replacing every occurrence of " (pt.)" you could press Find Next, which will go to the next occurrence of a cell of " (pt.)" without making a replacement.

[jimrtex]

No, it is not running in Protected Mode, and I merely copied and pasted the relevant parts of the Daily Kos spreadsheet onto a Excel spreadsheet, as jimrtex had earlier specified. And I've checked Options, and the boxes which you indicate are unchecked.

Is the Daily Kos spreadsheet still open in another window? That sometimes will affect the other windows. In my experience, having protected mode on in one Excel Workbook has a tendency to slow down and sometimes screw up other workbooks. It's probably best to close the Daily Kos spreadsheet, if it's still open.

The original source is a Google Docs spreadsheet.

Daily Kos Google Docs

We are copying to the clipboard and then pasting to a blank Excel Spreadsheet.

The cut from the Google Docs spreadsheet is retaining the formatting information in a way that Excel understands.

Column C in the Google Docs spreadsheet is just a spacer column, and conceivably might be locked, but I don't know for sure.

When I have pasted into the blank Excel spreadsheet I have pasted both with retaining the  formatting and removing the formatting, and in either case I can still do search and replace.

I've never really dealt with protected sheets.

If a cell were locked, you shouldn't be able to simply edit the cell NOR do a search and replace.

[Calthrina950]

I've checked, and the "Match entire cell contents" box is unchecked. However, I'm confused about what you outline to do after that-I'm still not too familiar with how the "Replace All" option works.

Was it checked, and then you unchecked it?

If it was checked, then when you searched on " (pt.)" it would only find cells that consisted entirely of " (pt.)". Since there were no such cells, it was returning a warning message. Unchecking it should let it find the cell "Glastonbury (pt.)" and replace the " (pt.)" with nothing.

If you can select Column C, then Ctrl-H, and enter the find string of " (pt.)" and the replace string of nothing, when you press Replace All does it make 10 replacements (or 9 if you have changed "Glastonbury (pt.)" by hand?

If so you can go to the next step.

OR, you can press Ctrl-Z to undo the replacement. And then with Column C selected, then Ctrl-H, press Find Next and it will highlight "Glastonbury (pt.)" or "Middletown (pt.). Pressing Replace will make the replacement in that cell only, and then advance to the next cell containing the search string.

If you had some reason for not replacing every occurrence of " (pt.)" you could press Find Next, which will go to the next occurrence of a cell of " (pt.)" without making a replacement.

No, it was unchecked, but I ran the CTRL +H formula again, and was able to get 10 replacements using Replace All. This is what the worksheet looks like now:

[cinyc]

No, it is not running in Protected Mode, and I merely copied and pasted the relevant parts of the Daily Kos spreadsheet onto a Excel spreadsheet, as jimrtex had earlier specified. And I've checked Options, and the boxes which you indicate are unchecked.

Is the Daily Kos spreadsheet still open in another window? That sometimes will affect the other windows. In my experience, having protected mode on in one Excel Workbook has a tendency to slow down and sometimes screw up other workbooks. It's probably best to close the Daily Kos spreadsheet, if it's still open.

The original source is a Google Docs spreadsheet.

Daily Kos Google Docs

We are copying to the clipboard and then pasting to a blank Excel Spreadsheet.

The cut from the Google Docs spreadsheet is retaining the formatting information in a way that Excel understands.

Column C in the Google Docs spreadsheet is just a spacer column, and conceivably might be locked, but I don't know for sure.

When I have pasted into the blank Excel spreadsheet I have pasted both with retaining the  formatting and removing the formatting, and in either case I can still do search and replace.

I've never really dealt with protected sheets.

If a cell were locked, you shouldn't be able to simply edit the cell NOR do a search and replace.

You can download a Google Sheet as an Excel File - just hit File-->Download-->Microsoft Excel. Usually, it preserves the formatting. When you open the sheet, though, a notice will likely pop up that it was downloaded from the internet and Excel is in protected mode. Excel thinks it's protecting you from viruses and stuff. You have to click on a box in the notice to get out of protected mode and edit the sheet.

[jimrtex]

No, it was unchecked, but I ran the CTRL +H formula again, and was able to get 10 replacements using Replace All. This is what the worksheet looks like now:

Have you deleted the summation rows for each district? Once you've done that, we can get Census Data that has town-county relationships.
Go to the Census Bureau website

(0) Look at the picture of Toksook Bay, the Dixville Notch of the 2020 Census, and shiver a bit. Then Click on Explore Data; Explore Data Main; and the box that says go to Data.Census.Gov. Click on Advanced Search.

(1) Click on Years, Select 2010 (the Census Year). Notice your filters show up at the bottom of the page (this is a tiny bit laggy, it won't appear instantly).



(2) Click on Surveys, Select DEC Summary File 1 (you will have to scroll down a bit). DEC is the Decennial Census. Summary File 1 (or SF1) is the main population data file for the Census.




(3) Click on Geography, County Subdivisions, Connecticut, All County Subdivisions within Connecticut.


In New England, county subdivisions are towns and cities. As you go west, there may also be cities that are separate subdivisions. By the time you get to Colorado county subdivisions are census constructs and useless.

(4) Click on Topics, Population and People, and Population and People.



(5) Click on Search (lower right corner) and View All Tables



(6) Scroll down and select Total Population, you will have to Load More several times.



This is clunky does anyone know a more efficient way?

Notice you now have a table of Connecticut towns with total population. There is a scroll bar at the very bottom of the page.

(7) Click on Customize Table, and then the Download Icon.


[size=14.6667pt](8) Make sure CSV is selected and then select Download.[/size]


(9) Then click on gray area below, to see progress bar. This may take a few seconds.



When it is 100% Complete click on Download Now.


(10) You will get this popup. Make sure Open With is selected, and you can click on Browse to confirm that Windows Explorer is the application that will open the Zip File. Click on OK.



It will take a bit of time to download. In Firefox, you can check the download progress by clicking the Down Arrow at the upper right of the browser window. A Zip File is a container for other files that are also compressed. Windows knows how to handle zip files, and you can pretty much treat it like a folder.


(11) When the download is complete, a Windows Explorer window will appear.



We want the largest file.

(12) Click on the largest file, holding the left mouse button down a drag the file to a directory for your current project.



(13) Right click on the .csv file, and Open with Excel.



If there is an Excel icon in front of the name, .csv files are associated with Excel, and you can just double click on the file.


(14) Your spreadsheet should look like this.


I have selected Cell B6, which shows that we have the town name and county name, which we will parse.

Column A is like an URL for Census Entities. 060 indicates it is a County Subdivision; US is for USA - there is at least an intent for Canada and Mexico to use the same standard format. 09 is the code for Connecticut. 001 is the code for the Fairfield County, and 18500 for Danbury.

Column B is the text identification. A county subdivision Danbury is a subdivision of Fairfield County, which is a county in Connecticut.

Column C is the 2010 Total Population of the town.

0600000US0900118500

Danbury town, Fairfield County, Connecticut

80893

[Calthrina950]

No, it was unchecked, but I ran the CTRL +H formula again, and was able to get 10 replacements using Replace All. This is what the worksheet looks like now:

Have you deleted the summation rows for each district? Once you've done that, we can get Census Data that has town-county relationships.
Go to the Census Bureau website

(0) Look at the picture of Toksook Bay, the Dixville Notch of the 2020 Census, and shiver a bit. Then Click on Explore Data; Explore Data Main; and the box that says go to Data.Census.Gov. Click on Advanced Search.

(1) Click on Years, Select 2010 (the Census Year). Notice your filters show up at the bottom of the page (this is a tiny bit laggy, it won't appear instantly).



(2) Click on Surveys, Select DEC Summary File 1 (you will have to scroll down a bit). DEC is the Decennial Census. Summary File 1 (or SF1) is the main population data file for the Census.




(3) Click on Geography, County Subdivisions, Connecticut, All County Subdivisions within Connecticut.


In New England, county subdivisions are towns and cities. As you go west, there may also be cities that are separate subdivisions. By the time you get to Colorado county subdivisions are census constructs and useless.

(4) Click on Topics, Population and People, and Population and People.



(5) Click on Search (lower right corner) and View All Tables



(6) Scroll down and select Total Population, you will have to Load More several times.



This is clunky does anyone know a more efficient way?

Notice you now have a table of Connecticut towns with total population. There is a scroll bar at the very bottom of the page.

(7) Click on Customize Table, and then the Download Icon.


[size=14.6667pt]( Make sure CSV is selected and then select Download.[/size]


(9) Then click on gray area below, to see progress bar. This may take a few seconds.



When it is 100% Complete click on Download Now.


(10) You will get this popup. Make sure Open With is selected, and you can click on Browse to confirm that Windows Explorer is the application that will open the Zip File. Click on OK.



It will take a bit of time to download. In Firefox, you can check the download progress by clicking the Down Arrow at the upper right of the browser window. A Zip File is a container for other files that are also compressed. Windows knows how to handle zip files, and you can pretty much treat it like a folder.


(11) When the download is complete, a Windows Explorer window will appear.



We want the largest file.

(12) Click on the largest file, holding the left mouse button down a drag the file to a directory for your current project.



(13) Right click on the .csv file, and Open with Excel.



If there is an Excel icon in front of the name, .csv files are associated with Excel, and you can just double click on the file.


(14) Your spreadsheet should look like this.


I have selected Cell B6, which shows that we have the town name and county name, which we will parse.

Column A is like an URL for Census Entities. 060 indicates it is a County Subdivision; US is for USA - there is at least an intent for Canada and Mexico to use the same standard format. 09 is the code for Connecticut. 001 is the code for the Fairfield County, and 18500 for Danbury.

Column B is the text identification. A county subdivision Danbury is a subdivision of Fairfield County, which is a county in Connecticut.

Column C is the 2010 Total Population of the town.

0600000US0900118500

Danbury town, Fairfield County, Connecticut

80893

I got all the way up to the stage with Window Explorer. However, I am confused by what you mean with "Directory" for my current project. Am I supposed to create a folder to contain the spreadsheets? Or is it something else that I do? I'm not exactly certain about this.

[jimrtex]

I got all the way up to the stage with Window Explorer. However, I am confused by what you mean with "Directory" for my current project. Am I supposed to create a folder to contain the spreadsheets? Or is it something else that I do? I'm not exactly certain about this.

Sorry, I used confusing terminology. You had written:

   "I wanted to use these numbers for an electoral scenario that I've worked on."

It is not clear what you mean (and that is OK). But you might be preparing a presentation for a class; or just for Atlas, etc. I find it convenient to place the files related to some project in a folder.

Directory is an alternative terminology to folder. "directory" is in an older term used on Unix and MS-DOS, and refers to the directory to a file system, where you would put pointers to a related set of files in a directory, and would use the DIR command to list them (I guess on Unix you would use the ls command to list the files). Folder is more modern term and is a metaphor for a file folder in a filing cabinet. When I look at a listing of the contents of a folder including attributes of the file such as name, file type, creation date, size, it looks like a directory listing to me.

[Calthrina950]

I got all the way up to the stage with Window Explorer. However, I am confused by what you mean with "Directory" for my current project. Am I supposed to create a folder to contain the spreadsheets? Or is it something else that I do? I'm not exactly certain about this.

Sorry, I used confusing terminology. You had written:

   "I wanted to use these numbers for an electoral scenario that I've worked on."

It is not clear what you mean (and that is OK). But you might be preparing a presentation for a class; or just for Atlas, etc. I find it convenient to place the files related to some project in a folder.

Directory is an alternative terminology to folder. "directory" is in an older term used on Unix and MS-DOS, and refers to the directory to a file system, where you would put pointers to a related set of files in a directory, and would use the DIR command to list them (I guess on Unix you would use the ls command to list the files). Folder is more modern term and is a metaphor for a file folder in a filing cabinet. When I look at a listing of the contents of a folder including attributes of the file such as name, file type, creation date, size, it looks like a directory listing to me.

This is a personal project, centered around this scenario: https://fiction.fandom.com/wiki/United_States_presidential_election,_2016_(Ferguson_Scenario). And I understand now what you mean by directory. The spreadsheet is in a folder, but it's a folder that includes work for the rest of the scenario. Could you reiterate your instructions again?

[jimrtex]

This is a personal project, centered around this scenario: https://fiction.fandom.com/wiki/United_States_presidential_election,_2016_(Ferguson_Scenario). And I understand now what you mean by directory. The spreadsheet is in a folder, but it's a folder that includes work for the rest of the scenario. Could you reiterate your instructions again?

There are two files.

(1) The file that you derived from the Daily Kos file. If you started with a New file, you will need to give it a name, and save it in your folder.

In Excel, Click File. then Save As. Give it a name that is meaningful to you (e.g. ct_2016_pres), make sure the file type is .xlsx and that it stored in your folder, you may have to Browse to get to this correct folder.

(2) The .csv file from the Census Data, which you dragged from the .zip file into your folder.

Rename the .csv file. Files from Census Data extracts tend to have meaningless, and duplicate names.

Then select the file, right click and open with Notepad. This just shows that a .csv file is a text file. This is useful for interchange of data, but lacks the structure and formatting of a spreadsheet, and can't contain formulas. Close the file - this step was just to look at a .csv file.

Select the .csv file, right click and open with Excel. Select File, Save As, and save as an .xlsx file. Change the file type to .xlsx. The name and location of the .xlsx file should be the same as the .csv file, but check to make sure.

Note that this will not change the appearance of the file in Excel but will create the underlying file.

[Calthrina950]

Bumping this up. I'm wondering if anyone else has insight regarding this question. I have the Rhode Island results already, but I'm trying to save time in obtaining those for Connecticut. Are there any other websites that might have the data for those districts?

[Calthrina950]

I'm pushing this forward. Again, are there any other data sources for how Connecticut's congressional districts voted, by county, in the 2016 presidential election?

[jimrtex]

I hadn't posted this before. Starting with the file we downloaded from the Census Bureau, delete the rows with 0 population, that say they are not a county subdivision. These are in Long Island Sound, and are considered part of a county, but not a town.

(0) This shows the content of B3, and illustrates where we will divide it.



We are going to parse the string to the left of the first comma.

(1) Begin typing in D3:  =Le      As you type functions that match will appear.



(2) Double click on LEFT. This will complete the spelling of LEFT and add the opening paren.
Note that a help for the function arguments appears below. If this is in your way, you can drag it elsewhere.



(3) Click on the cell B3, which will enter its address in the function. Cells used for function arguments will be highlighted.



(4) Enter a comma, and type FI the first letters of FIND.



(5) Double Click on FIND, which adds the opening parentheses. Enter ",", (quote, comma, quote, comma) and click on cell B3.



(6) Enter )-1  (close paren, minus-sign, numeral 1)



(7) Enter ) (close paren)  Notice the completed expression of

=LEFT(B3,FIND(",",B3)-1)

We want the left portion of B3, before the first comma. FIND(",",B3) finds the position of the comma. We subtract one because we don't want the comma.



( Press the Enter Key, The function is now evaluated.



The value of D3 is:  Danbury town which is what we wanted.

[Calthrina950]

I hadn't posted this before. Starting with the file we downloaded from the Census Bureau, delete the rows with 0 population, that say they are not a county subdivision. These are in Long Island Sound, and are considered part of a county, but not a town.

(0) This shows the content of B3, and illustrates where we will divide it.



We are going to parse the string to the left of the first comma.

(1) Begin typing in D3:  =Le      As you type functions that match will appear.



(2) Double click on LEFT. This will complete the spelling of LEFT and add the opening paren.
Note that a help for the function arguments appears below. If this is in your way, you can drag it elsewhere.



(3) Click on the cell B3, which will enter its address in the function. Cells used for function arguments will be highlighted.



(4) Enter a comma, and type FI the first letters of FIND.



(5) Double Click on FIND, which adds the opening parentheses. Enter ",", (quote, comma, quote, comma) and click on cell B3.



(6) Enter )-1  (close paren, minus-sign, numeral 1)



(7) Enter ) (close paren)  Notice the completed expression of

=LEFT(B3,FIND(",",B3)-1)

We want the left portion of B3, before the first comma. FIND(",",B3) finds the position of the comma. We subtract one because we don't want the comma.



( Press the Enter Key, The function is now evaluated.



The value of D3 is:  Danbury town which is what we wanted.

I've done all of this, and all of what you outlined in the previous post. I renamed the file "Data Overlay", and saved it as an Excel Workbook. What is the next step?

[jimrtex]

We are now going to copy the formula down column D.

(9A) Select Cell D3. Then while holding the shift-key down, go down with the down arrow or page down key or a combination of both.



(9B) While continuing to hold the shift-key down, continue down until you have reached the last entry. If you overshoot, you can back up with the up arrow. You have now selected the region D3:D171



(10) Release the shift key, and press CTRL-D (D as in down). This copies the formula in the top row of the selected area, to the bottom. Note that the formula is automatically incremented, so that D171 references C171.



(11) We are now going to convert the formulas to actual data values. While the area is still selected, press CTRL-C to copy to the clipboard. Then click on the little arrow below the Paste icon in the upper left, to get a pull down menu.



(12) Click on the icon with below Paste values that says 123. This converts the formula in the clipboard to the evaluated values. Note that the formula bar reads Orange Town rather than =LEFT(B171,FIND(",",B171)-1)



(13) We are now going to edit the " town" from each town. We want to match the values in the election results spreadsheet. This is an alternate way to select a region. Select cell D3 at the top of the column. Then while holding the shift-key press End then the Down Arrow. This goes to the last row that is not blank.



(14)  Press CTRL-H to get the find and replace menu. For the Find What: (sp)town
That is, one space before town. For the Replace With: make sure there is nothing. And select the Match Case option. We want to take a little bit of care that we don't convert "Middletown town" into "Middle". Click on Replace All, and Close the dialogue.

If you mess up, you can back up with CTRL-Z. In other states, not all of the county subdivisions will be towns. So you might have to also strip off " city". Connecticut has cities, but they are contained within a town, even if they are coterminous. In Massachusetts and New York some county subdivisions are cities rather than towns. For example Hudson city is treated as a county subdivision in Columbia County along with the other towns.



(15) We are now going to isolate the county from B3 etc,

In E3 construct the formula in the Formula Bar

=RIGHT(B3,LEN(B3)-FIND(",",B3)-1)

You can type the entire formula, but I find it simpler to construct formulas. This formula takes the right portion of the value in B3. The number of characters on the right is the length of the string minus the position of the first comma minus one).



(16) Press enter, and check that we got the right string. If we took one to few characters, we would have "airfield County, Connecticut", if we took too many their would be a leading space. If we make a mistake, CTRL-Z to undo, and correct the formula. You can click on the formula in the formula bar and use the backspace, delete, or ordinary keys. For example if we had typed -2 instead of -1, which gave us "airfield", simply select E3, click just behind "2" in the formula, backspace, and press 1. Then press enter.



(17) We are now going to copy the formula down Column E. This is an alternative way to select a region. If you only have 170 rows it is simple to quickly page to the end. If you have several 1000 rows it is not so quick.

First while E3 is selected, Hold the shift-key press end and the down arrow. You have selected E3:E1048576 which is the maximum row that Excel can handle.

That is not what you want. So Select D3, Then End and the down arrow. This will naviagte to D171. Then use the right arrow to move to E3. Holding the shift key, press end and the up arrow. This will take you up to through the first row that is not blank (i.e. E3). Press CTRL-D to copy the formula down the column.



Go ahead and convert the formulas to data values: (a) Select the region you want to convert. (b) CTRL-C to copy to clipboard, (c) Paste Values to same region.

(18) We are now going to strip off the (sp)County,(sp)Conecticut. Rather than typing that in. We copy it from the contents of E3, Select E3. Move the cursor to the Formula Bar, it will switch from the + cursor to a text entry cursor (vertical bar). Position after the d in Fairfield, and with a click hold move to the end of the string (it will be highlighted in gray Click on CTRL-C to copy the selected text.



(19) Select the region to be edited. Then press CTRL-H and then placing the cursor in the Find What box, press CTRL-V and then Replace All.



(20) We now have a file that contains town-county pairs. We can set this file aside, in case we have another data set organized by CD (let's say that someone posts the primary results, and you want to convert to county. Or maybe the SOS will post results by county and you want to convert that to CD,

But for now, we just want to associate the towns in your data file with their counties. We can work between two spreadsheets, but it is a little easier to work in a single spreadsheet.

I have simply copied the contents of the file we just edited into the data worksheet. Select A2, then while holding the shift-key End Down-Arrow to get to the end of Column A, then use the Right-Arrow to expand the region to the right. Press CTRL-C to copy. Go to the data spreadsheet, select cell N1 and press CTRL-V to paste it.

[Calthrina950]

We are now going to copy the formula down column D.

(9A) Select Cell D3. Then while holding the shift-key down, go down with the down arrow or page down key or a combination of both.



(9B) While continuing to hold the shift-key down, continue down until you have reached the last entry. If you overshoot, you can back up with the up arrow. You have now selected the region D3:D171



(10) Release the shift key, and press CTRL-D (D as in down). This copies the formula in the top row of the selected area, to the bottom. Note that the formula is automatically incremented, so that D171 references C171.



(11) We are now going to convert the formulas to actual data values. While the area is still selected, press CTRL-C to copy to the clipboard. Then click on the little arrow below the Paste icon in the upper left, to get a pull down menu.



(12) Click on the icon with below Paste values that says 123. This converts the formula in the clipboard to the evaluated values. Note that the formula bar reads Orange Town rather than =LEFT(B171,FIND(",",B171)-1)



(13) We are now going to edit the " town" from each town. We want to match the values in the election results spreadsheet. This is an alternate way to select a region. Select cell D3 at the top of the column. Then while holding the shift-key press End then the Down Arrow. This goes to the last row that is not blank.



(14)  Press CTRL-H to get the find and replace menu. For the Find What: (sp)town
That is, one space before town. For the Replace With: make sure there is nothing. And select the Match Case option. We want to take a little bit of care that we don't convert "Middletown town" into "Middle". Click on Replace All, and Close the dialogue.

If you mess up, you can back up with CTRL-Z. In other states, not all of the county subdivisions will be towns. So you might have to also strip off " city". Connecticut has cities, but they are contained within a town, even if they are coterminous. In Massachusetts and New York some county subdivisions are cities rather than towns. For example Hudson city is treated as a county subdivision in Columbia County along with the other towns.



(15) We are now going to isolate the county from B3 etc,

In E3 construct the formula in the Formula Bar

=RIGHT(B3,LEN(B3)-FIND(",",B3)-1)

You can type the entire formula, but I find it simpler to construct formulas. This formula takes the right portion of the value in B3. The number of characters on the right is the length of the string minus the position of the first comma minus one).



(16) Press enter, and check that we got the right string. If we took one to few characters, we would have "airfield County, Connecticut", if we took too many their would be a leading space. If we make a mistake, CTRL-Z to undo, and correct the formula. You can click on the formula in the formula bar and use the backspace, delete, or ordinary keys. For example if we had typed -2 instead of -1, which gave us "airfield", simply select E3, click just behind "2" in the formula, backspace, and press 1. Then press enter.



(17) We are now going to copy the formula down Column E. This is an alternative way to select a region. If you only have 170 rows it is simple to quickly page to the end. If you have several 1000 rows it is not so quick.

First while E3 is selected, Hold the shift-key press end and the down arrow. You have selected E3:E1048576 which is the maximum row that Excel can handle.

That is not what you want. So Select D3, Then End and the down arrow. This will naviagte to D171. Then use the right arrow to move to E3. Holding the shift key, press end and the up arrow. This will take you up to through the first row that is not blank (i.e. E3). Press CTRL-D to copy the formula down the column.



Go ahead and convert the formulas to data values: (a) Select the region you want to convert. (b) CTRL-C to copy to clipboard, (c) Paste Values to same region.

(18) We are now going to strip off the (sp)County,(sp)Conecticut. Rather than typing that in. We copy it from the contents of E3, Select E3. Move the cursor to the Formula Bar, it will switch from the + cursor to a text entry cursor (vertical bar). Position after the d in Fairfield, and with a click hold move to the end of the string (it will be highlighted in gray Click on CTRL-C to copy the selected text.



(19) Select the region to be edited. Then press CTRL-H and then placing the cursor in the Find What box, press CTRL-V and then Replace All.



(20) We now have a file that contains town-county pairs. We can set this file aside, in case we have another data set organized by CD (let's say that someone posts the primary results, and you want to convert to county. Or maybe the SOS will post results by county and you want to convert that to CD,

But for now, we just want to associate the towns in your data file with their counties. We can work between two spreadsheets, but it is a little easier to work in a single spreadsheet.

I have simply copied the contents of the file we just edited into the data worksheet. Select A2, then while holding the shift-key End Down-Arrow to get to the end of Column A, then use the Right-Arrow to expand the region to the right. Press CTRL-C to copy. Go to the data spreadsheet, select cell N1 and press CTRL-V to paste it.

I was able to do it up to Step 13, but now I'm receiving an error message:

[jimrtex]

I was able to do it up to Step 13, but now I'm receiving an error message:

What did the find and replace dialogue look like before the error message covered it up?

[Calthrina950]

I was able to do it up to Step 13, but now I'm receiving an error message:

What did the find and replace dialogue look like before the error message covered it up?

It looked as you had stated:

[jimrtex]

I was able to do it up to Step 13, but now I'm receiving an error message:

What did the find and replace dialogue look like before the error message covered it up?

It looked as you had stated:

I confused you.

I meant " town" without the quotation marks. The (sp) was indicating that there was a leading non-visible space character.

[Calthrina950]

I was able to do it up to Step 13, but now I'm receiving an error message:

What did the find and replace dialogue look like before the error message covered it up?

It looked as you had stated:

I confused you.

I meant " town" without the quotation marks. The (sp) was indicating that there was a leading non-visible space character.

I went back and got it, and this is what I have now:

I've done everything up to Step 16.

[jimrtex]

I went back and got it, and this is what I have now:

I've done everything up to Step 16.

What is in Cell E3. Select E3, and show what is in the Formula Bar.

[Calthrina950]

I went back and got it, and this is what I have now:

I've done everything up to Step 16.

What is in Cell E3. Select E3, and show what is in the Formula Bar.

I managed to get past Step 16, and it now looks like this:

[Calthrina950]

I'm bumping this forward, because I would like for any additional insights. If anyone has any information on how to calculate the Connecticut congressional districts, broken down by county, I would greatly appreciate it.