Title: NH RMH Test2 Original CoS Document (slug): [[https://conventionofstates.com/files/nh-rmh-test2|nh-rmh-test2]] Login Required to view? No Attached File: Process_Petition_List_Guide_20230328(1).docx Version 23042 Created: 2023-04-24 14:38:12 Updated: 2024-04-24 23:00:00 Published: 2023-04-23 21:00:00 Converted: 2025-04-14T20:11:54.322464672 ---- ====== Contents ====== [[#revisions|Revisions 2]] [[#overview|Overview 3]] [[#export-a-petitions.csv-file|Export a petitions.csv file 4]] [[#method-one-works-for-everyone|Method One (works for everyone) 4]] [[#method-two-shorter|Method Two (shorter) 6]] [[#enable-macros|Enable Macros 7]] [[#open-the-process-petition-list-file|Open the Process Petition List file 8]] [[#import-a-petitions.csv-file|Import a petitions.csv file 9]] [[#classify-cities|Classify cities 10]] [[#check-for-errors|Check For Errors 11]] [[#a-cheat---and-the-tradeoffs|A Cheat - and the Tradeoffs 12]] [[#how-to-fix-most-errors-data-cleaning|How to Fix Most Errors – Data Cleaning 13]] [[#false-positives-good-data-highlighted-as-bad|False Positives (good data highlighted as BAD) 15]] [[#the-last-one-percent|The Last One Percent 17]] [[#prepare-for-final-publication|Prepare for Final Publication 18]] [[#archive-highlighted-rows|Archive Highlighted Rows 18]] [[#final-formatting|Final Formatting 18]] [[#print-at-home|Print at Home 18]] [[#print-at-staples-office-depot-etc.|Print at Staples, Office Depot, etc. 18]] [[#data-cleaning-via-the-archive-tab|Data Cleaning via the Archive tab 19]] [[#acknowledgements|Acknowledgements 19]] [[#appendix-macros|Appendix – Macros 20]] [[#appendix-edit-print-header|Appendix – Edit Print Header 24]] [[#appendix-one-page-instructions|Appendix – One-Page Instructions 26]] [[#appendix-developer-notes|Appendix – Developer Notes 27]] ====== Revisions ====== ^Date ^Description ^Author ^ |1/19/22 |Added revisions section. Added a dialog box and explanation in the appendix. Added section about using Archive tab for data cleaning.|Bill Wiltschko | |1/20/2022 |Added one-page instructions as second appendix. |Bill Wiltschko | |1/24/22 |Added appendix for editing Print Titles. |Bill Wiltschko | |1/25/2022 |Added internal hyperlinks to appendices. |Bill Wiltschko | |2/9/2022 |Added note to leave a good city in column A if “bad” city is must mis-spelled (as opposed to out-of-district). |Bill Wiltschko | |3/19/2022 |Fixed bug in Cities() processing in CheckTable sub, and in NoNum Function. No change to Guide. |Bill Wiltschko | |7/22/2022 |Fixed false positive issue with addresses that start with “HC” (highway contract) and “RR” (rural route), particularly for Oklahoma. |Bill Wiltschko | |11/25/2022|Added graphic to Overview |Bill Wiltschko | |12/1/2022 |Added Developer’s Note appendix |Bill Wiltschko | |12/4/2022 |Added information about how to buy Excel |Bill Wiltschko | |12/17/2022|Allowed N,S,E,W as first letter in Address. Added check for any Cities in Column A of Cities tab that aren’t capitalized. |Bill Wiltschko | |12/26/2022|Added note about pink “blocked” message.\\ \\ Highlighted both records when duplicate is found. |Bill Wiltschko | |12/28/2022|Now look inside words in address for all caps, no caps, etc. |Bill Wiltschko | |1/15/2023 |Fixed sort problem for people named “True”. Fixed problem with Street names having State names such as “Ohio 35”, “US 20” as street. |Bill Wiltschko | |1/17/2023 |Fixed print preview problem with large lists |Bill Wiltschko | |2/8/2023 |Major revisions to handle new exported petitions.csv files that have two columns added: Middle name and Deceased. Total 12 columns. |Bill Wiltschko\\ \\ Rick Raymond| |2/15/2023 |Corrected for bug that removed zip code leading zeros in csv file. |Bill Wiltschko\\ \\ Rick Raymond| |2/16/2023 |Added comprehensive un-highlight/re-highlight functionality to handle false positives more easily. |Bill Wiltschko\\ \\ Rick Raymond| |2/18/2023 |Allowed foreign characters in names (not addresses). Fixed bug that didn’t catch lower case characters in first letter of names. |Bill Wiltschko | |2/19/2023 |Updated “How to fix data errors” section |Bill Wiltschko | |3/28/2023 |Bug fix affecting Mac users |Martin Nolan | The latest Excel program file is always found here: > https://conventionofstates.com/files/process-petition-list The latest guide is always here (MS Word): > https://conventionofstates.com/files/process-petition-list-guide The legacy Excel program file for 10-column petitions.csv files is here: https://conventionofstates.com/files/process-petition-list-legacy Recording of training:\\ https://rumble.com/v20zmyc-process-petition-list.html The “meat” starts at 24:30 ====== Overview ====== The Process Petition List macro-enabled Excel file transforms a petitions.csv file exported from the Legislative Management System (LMS) into a nicely-formatted printed list of petition signers suitable for handing to legislators. This document explains how to: - Export a petitions.csv file from the LMS - Import petitions.csv file into the Process Petition List program - Highlight data problems in the list - Fix problems at the source (Citizen Builder, usually) - Print a clean list for legislators Visually, {{:cb_mirror_public::media:image1.png}} **YOU MUST HAVE EXCEL** ON EITHER A PC OR A MAC FOR THIS PROCESS TO WORK!! If you do not have Excel, you have several options: - Buy MS Office (you don’t need Pro to get Excel) from Microsoft or a software reseller. Price varies widely depending on what kind of user you are, but start at $100. - Buy a subscription to Microsoft Office 365 aka office.com aka Microsoft 365. The Personal version is ~60/yr for the auto-renewal version or ~140/yr otherwise. - Buy MS Office from [[https://www.ebay.com/|eBay]], but do not consider anything that costs about $10. Expect to pay $50 to $150 for a Pro version you can activate and get updates for. Availability varies. ====== Export a petitions.csv file ====== First gain access to the Legislative Management System (LMS). Your state director can grant you access. Two processes can export a petitions.csv file. Some will be able to use both, but some may only be able to use the first method, especially if they have access to only one lower house district. ===== Method One (works for everyone) ===== {{:cb_mirror_public::media:image2.png}}Go to the LMS (coslms.com), choose **Legislators** near the top of the left navigation pane. You will get this. * Choose the **latest** Session * Choose your chamber (Assembly/House or Senate) * Select "Show 100 rows" rather than 10 rows * Sort by district if you can see more than one district * Find your district {{:cb_mirror_public::media:image3.png}}Click on the **LEGISLATOR** (in Name column) for that district, not the district number. You will then see this: > {{:cb_mirror_public::media:image4.png}}Click on the gray Petitions button. You will see the following screen. Click Export button, choose **"CSV"** It will take a minute or two to generate the CSV file after clicking "CSV" If an “Opening” dialog box opens, like this: {{:cb_mirror_public::media:image5.png}} Click **Save File** and save to your **local** drive**.** //Macros may not work// if you merely open it. Find the saved file (typically in the downloads folder) and Open it in Excel. You MUST open it in Excel. If you have another spreadsheet program as well as Excel, right click the file and “Open with” Excel. ===== Method Two (shorter) ===== This method may only be available to those who can see multiple districts. Go to the LMS (coslms.com), choose **Tools** at the bottom of the left menu > Then choose **Reports** > > Then choose **Petitions** under the list of reports Choose your **State**. Enter your **District (**e.g., "CA 29"**)**. Only ONE district! > Note: the program works fine on multiple districts, including whole states. The only downside is that the titles on the printed pages will not be correct. To fix these see Appendix – Edit Print Header Click the **"Export" button** on the row of boxes **UNDER** the District boxes and choose **CSV.** {{:cb_mirror_public::media:image6.png}} It will take a minute or two to export the CSV file after clicking "CSV". It will be saved to your default download folder. If an “Opening” dialog box opens, like this: {{:cb_mirror_public::media:image5.png}} Click **Save File** and save to your **local** drive**.** //Macros may not work// if you merely open it. Find the saved file (typically in the downloads folder) and Open it in Excel. You MUST open it in Excel. If you have another spreadsheet program as well as Excel, right click the file and “Open with” Excel. ====== Enable Macros ====== If you see a red message bar at the top of the screen, close the file, go into file explorer and right click on the file. Then select properties, and click the box next to “unblock” at the bottom of the dialog. Close the dialog and reopen the file. {{:cb_mirror_public::media:image8.png}}Click the “Enable Editing” or “Enable Content” button (sometimes both) in the yellow Message Bar at the top of the window when you open an Excel file, like below: You may have to click one or both of these buttons for both the petitions.csv file and the Process Petition List file (we open this file in the next section). This is safe because we know the origins of both files. After enabling content or enabling editing, you will see **four buttons** at the top of the Table tab (tabs are also called sheets or worksheets) to the right of the data table, in the Process Petition List file. If you do not see four buttons, scroll to the right to make sure they are really missing, then try the techniques below. The indication that macros have been enabled is seeing **four buttons** in the top right part of the Table tab in the Process Petition List file!! If you cannot enable macros - that is if you do not see **four buttons** - please see the Appendix – Macros ====== Open the Process Petition List file ====== The latest version of the Process Petition List program can always be found here: [[https://conventionofstates.com/files/process-petition-list|**https:%%//%%conventionofstates.com/files/process-petition-list**]] You MUST open it in Excel on either a PC or a Mac. If you have another spreadsheet program as well as Excel, you might have to right click the file and “Open with” Excel. The macros will not work if you use Google Sheets, LibreOffice, Numbers, or OpenOffice. > If you get an error in a **PINK** bar at the top of the screen, as opposed to a yellow bar, that says the file has been “blocked”:
  1. >

    Close the file.

  2. >

    Find the file in File Explorer. Right click the filename.

  3. >

    Click Properties

  4. >

    At the bottom of the dialog, check the “Unblock” box.

  5. >

    Close the dialog, re-open the file.

{{:cb_mirror_public::media:image9.png}}The file will open showing the Table tab (tabs are also called sheets or worksheets) at the bottom of the window. ^Tab name ^Description ^ |Table |Holds all the raw data, at first, and will have all the cleaned data at the end. It also has all **four buttons** that start macros to automate the work.| |Cities |List of the unique cities in the Table tab. We move “bad” cities from column A to column B on this tab. | |Interpretation|Explanation for all the little cryptic notes in Table tab column N. These explain what error was found that merited highlighting a cell. | |Notes |Concise description about how to use the file. This document expands on that. | |Archive |Holds all the rows with errors that were left out of the list we give the legislators. | ====== Import a petitions.csv file ====== This is easy: open both files, click one button. - Open the petitions.csv file exported above. Open it and click “Enable Editing” or “Enable Content” wherever you see it. You do not need to manipulate the file; you only need to open it. Only one petitions.csv file can be open at a time. - Open the Process Petition List file. Click “Enable Editing” or “Enable Content” if necessary. Make sure you can see **four buttons** in the upper right corner of the Tables tab (tabs are also called sheets or worksheets). - {{:cb_mirror_public::media:image10.png}}Click the first of the **four buttons**, named “Copy and Paste CSV Data” ===== Classify cities ===== {{:cb_mirror_public::media:image9.png}}This step does not involve clicking any buttons. Instead, go to the Cities tab. Tabs (tabs are also called sheets or worksheets) are shown at the bottom of the window: The Cities tab will show all the unique cities, sorted, from the petitions.csv file in column A. There may or may not be left-over cities in column B from the last time the file was used. **Delete** any left-overs in column B only. Inspect the cities in column A for: Drag “bad” cities into column B. They can be anywhere in column B. They will persist in column B, no matter how many times a petitions.csv file is imported, until a user deletes them. If a “bad” city is a variation (mis-spelling) of a “good” city, **make sure** a “good” city is in Column A. The decision about what is “bad” can also be about standardization. For example, in CA AD 30, “Carmel By The Sea” is spelled many ways in AD 30 CB profiles: > Carmel By The Sea > > Carmel by the sea > > Carmel-by-the-sea > > Carmel-By-The-Sea Note that half have bad capitalization, which will be flagged by the Check Table macro. It is the user’s choice how to standardize. Put the standardized version in column A and non-standard versions in column B. The goal is a professional look. ====== Check For Errors ====== Check for errors by clicking the “Check Table” button. The macro checks for the following errors: - Cells with missing values will be highlighted. - Street addresses that don't start with a number or "PO Box " (case sensitive, note trailing space) will be highlighted. - Street numbers are allowed to start with N,S,E,W, HC or RR followed by an optional space and the number. - Street addresses will be checked for repeated items, and for city, state, and zip (which have their own columns). - Possible duplicate rows will be highlighted. - Names and Addresses will be checked for invalid characters. - Leading double caps, all caps, and all lower case in names, street addresses, will be highlighted. Note exceptions mentioned above. - Cities will be checked for all caps, all lower case, and lower case first letter. - Cities will be checked against cities in the district (see Cities tab column A for list) and will be highlighted if missing. Don't be alarmed if you see "Not Responding" at the top of the Excel window. Excel is just working hard. When an error is found - Each cell with an error will be highlighted yellow. - The first cell (signed date) in any row having an error will be highlighted yellow. - A cell with an error will have a hyperlink added that points to the specific Citizen Builder (CB) profile and the specific tab in that profile where the information can be corrected. - A cryptic text string hinting at the kind of error will be placed in column N. A legend for these cryptic text strings is in the Interpretation tab, just to the right of the Cities tab at the bottom of the window. The “Check Table” macro can take as much as a ten seconds per one thousand rows. It depends on the speed of your machine. Newer machines take much less time. Keep in mind that human-entered data, which is what all the data in Citizen Builder is, has an infinite variety of possible error. No machine or program can possibly have zero false positives (incorrectly identified as errors) or zero false negatives (errors that should have been caught). The Process Petition List file reduces the work of finding and fixing errors, but ALWAYS CHECK the entire file for anything unexpected. The most common issue may be other kinds of duplicates that the program missed. Please report any issues you find by submitting a help desk ticket with “Process Petition List” in the subject; we may be able to find a way to catch it. ===== A Cheat - and the Tradeoffs ===== The next step is to clean the data. When you are in a hurry to meet a deadline, such as an imminent meeting with your legislator, it is possible to skip data cleaning. This postpones the data cleaning process. The shortest path to a pretty-clean list of petition signers for legislators is to: - Open the petitions.csv file - Click each of the four buttons on the Table tab in order. - Print the result This short path can be improved by: - After clicking the first button, move “bad” cities from column A to column B of the Cities tab. - After clicking the second button, look over the result for false positives and false negatives, as you would normally do. There is one-page “cheat sheet” in the appendix - Appendix – One-Page Instructions ===== ===== Tradeoffs The typical percentage of rows with errors in a district which has never been cleaned before is about ten percent of all rows. If they are not cleaned up, if they remain highlighted after the “Check Table” button is clicked, and if we do the cheat, that will mean a reduction of total rows we show legislators by ten percent. The typical percentage of rows with errors that cannot be cleaned, even after using all the techniques described below, is about one percent. Thus, the cheat will reduce your list of petition signers by about nine percent compared to fixing data errors at the source. You will still have the same errors to deal with for later legislator visits, for both upper and lower house legislators. Bottom line: It is better to fix errors in Citizen Builder and process another petitions.csv file. ===== How to Fix Most Errors – Data Cleaning ===== It is a good idea to filter by color to show just the rows with errors (yellow highlighting), hiding all the “good” rows. Note that because there will be both false positives and false negatives, there is no substitute for reviewing all rows, even if rapidly. {{:cb_mirror_public::media:image12.png}} Right click the little button on the right side of cell A1, “Signed Date”, in the Table tab. Select “Filter by color” and choose bright yellow (should be the only color showing). This concentrates all the questionable rows in a smaller space. The screen shot to the left is taken just before clicking on the yellow rectangle, which will execute the filter. Undo the filter on cell color by clicking again on the little button on the right side of cell A1, “Signed Date”, in the Table tab. This time click on “Clear Filter From “Signed Date””, which is just above the Filter by Color option in the screenshot to the left. Expect approximately 10% of the rows to have some kind of error. It is usually possible to reduce the % of rows with errors to about 1%. Most errors are fixed by editing their text in the Citizen Builder (CB) profile. Duplicates are more complicated. The user can go directly to the data source in the CB profile by: - Signin to COS using default browser - Clicking on a highlighted cell will take you to the CB profile tab that has the problem. - For duplicates, clicking on the **Last Name** cell takes the user to the “duplicate” tab of the CB profile. Specific Error Fixes ^Error Type ^What to do ^ |Spelling, capitalization|Edit the text. Use the CB profile edit tab for names.\\ \\ Use CB profile’s address tab for address; clicking the ADDRESS LOOKUP button often fixes spelling and capitalization in addresses. See more below. Click UPDATE to save changes. | |Names |The CB profile’s edit tab has the name. Sometimes the name looks good in the CB profile but weird characters appear in the Table; just delete and re-type the name in the CB profile.\\ \\ URLs in highlighted names cells go to the specific profile and tab to make changes. | |Address |* URLs in highlighted address cells go to the specific profile’s address tab to make changes.\\ * Edit typos in the CB profile’s address tab.\\ * Or, click on the ADDRESS LOOKUP button in the profile’s address tab. Accept defaults and click “CHECK” button. It will almost always provide a better address, including often a zip+4. CB will format the address according to USPS conventions. If you like what it brings back, click “FILL ADDRESS WITH RESULT”. Click UPDATE to save changes.\\ * If clicking the CHECK button in ADDRESS LOOKUP returns nothing useful, try the “SEARCH” button. Try changing from “Progressive” to “Loose”. This does a Melissa lookup.\\ * If the ADDRESS LOOKUP function returns a radically different address, consider using a site like Spokeo (costs money) to find a good address. The supporter may have moved. The ultimate authority is the supporter; don’t hesitate to contact him/her.\\ * Addresses will rarely be found for a blank street address in the profile. These are often unrecoverable.\\ * An address might be valid but highlighted as bad. Select all cells in the row, from Signed Date column to the Deceased column. Merely selecting these cells will unhighlight them.| |Duplicate |* First verify that the duplicate is real. Sometimes first names will vary but be the same person: Jim versus James. Or different people: Rebecca versus Robert.\\ * Sometimes highlighted duplicates are not real duplicates. If this is the case, we have a false positive. Select cells in the row from Signed Date to Deceased. Merely selecting these cells will unhighlight them.\\ * The Last name hyperlink takes you to the CB profile’s duplicate tab. Note that a duplicate profile may already appear in the right side of the screen. If the two people appear to be the same person based on all available data, click the “Split Person” icon to the right. If you get an error message when you attempt this, submit a help desk ticket (https://help.conventionofstates.com) with the two names and CBIDs as text to have an SIA do it.\\ * If a dupe profile does not appear on the right side of the screen, input the CBID of the other profile into the search box. Wait a few seconds for a name to appear, then click on that name, then “Merge”. Again, if this isn’t allowed, or it’s confusing, submit a help desk ticket with the two names and CBIDs as text. | ===== False Positives (good data highlighted as BAD) ===== Sometimes good data will be highlighted as bad. There are many variations of street address standards across the fifty states. In some states, for example, all street addresses start with a pure number (or “PO Box”). In some a compass direction letter is allow to be a leading character. In others any character can be a leading character. The current program allows leading characters N, S, E, W, HC:, or RR before the street number. Most other leading letters cause the address to be marked as bad. Some street addresses have street names that include some element of city, state, or country. For example, “Ohio 25” or “US 10” or “Springvale Rd” (in a town called Springvale). These examples are good addresses. Sometimes the user has typed the city, state, or zip code on to the end of an otherwise valid street address, which isn’t good. The Process Petition program attempts to properly classify street addresses, but human ingenuity often out-foxes it. If a cell is incorrectly highlighted as bad, see next section; you have a false positive. **There is now an easy way to handle false positives.** Select an entire row inside the table, from the first “Signed Date” column to the “Deceased” column (Columns A through L, 12 columns). Merely //selecting// it makes three things happen: - UN-highlights highlighted cells - Removes hyperlinks - Adds a row in the False Pos sheet. This is persistent and will prevent the row from being highlighted again by the Check Table macro. Rows can be re-highlighted if necessary. For this to happen, please select //more// of the row, from the “Signed Date” column to the column with the error codes (Columns A through N, 14 columns). Merely //selecting// these cells makes three things happen: - RE-highlights the exact same cells as the original - Adds back the hyperlinks the cells had originally - Removes relevant row from the False Pos sheet. If the Check Table macro is run again, this row will now be highlighted again if it hasn’t been edited. Multiple rows can be selected for un-highlighting (12 columns) and re-highlighting (14 columns). When multiple cells in a row are highlighted and one of them is a false positive (good data marked bad), FIRST fix the legitimately incorrect cells in CB by following the hyperlinks in the cells, THEN unhighlight the entire row. When the updated petitions.csv file from the LMS is imported again, the Check Table macro will not mark it as bad. {{:cb_mirror_public::media:image13.png}}{{:cb_mirror_public::media:image15.png}}**There is a harder way to unhighlight false positives, just in case**. S**elect ALL** the cells in the row. This will include columns A through L for that row. Then, unhighlight the row by first clicking the paint can on the ribbon, then clicking on “No Fill”. You can select multiple rows by holding down the Ctrl key while selecting each row. Then unhighlight the selected rows by clicking the paint can and “No Fill”. The method has the disadvantage of not remembering that it is a false positive; the row is not saved to the False Pos sheet automatically. However, you can manually copy and paste it into the False Pos sheet, which will prevent it from being highlighted again when the Check Table macro is run again. It isn’t strictly necessary to copy the entire row into the False Pos sheet. The only False Pos sheet column needed to prevent re-highlighting by the Check Table macro is the CB ID column. ===== The Last One Percent ===== About one percent of the CB profile errors cannot be fixed. These profiles may not have a first name, last name, or street address, or it may have a really mangled street address. Submit a help desk ticket (https://help.conventionofstates.com) with the names and CBIDs of the CB profiles you cannot fix. The help desk can ban a profile if it has an email address (to prevent email address re-use), or delete it if it doesn’t. DO NOT mark them as deceased, which will create additional problems. The help desk can merge duplicates you cannot merge, namely where at least one of the profiles has a confirmed account. While waiting for the ticket to be worked, leave the rows highlighted, and click the “Archive Highlighted Rows” button to send them to the Archive tab. You can do this AND submit a ticket. ====== Prepare for Final Publication ====== After fixing data errors, export a brand-new petitions.csv file that will show your corrections. - Run the Copy and Paste CSV Data and “Check Table” macros on the new data. - **Manually highlight** the entire row for any false negatives (real problems not highlighted by the macro). - **Un-Highlight** the entire row for any false positives (marked as a bad but are NOT bad). This can be done manually, or it can be done faster using the method in False Positives (good data highlighted as BAD) section above. ===== Archive Highlighted Rows ===== Click the third, “Archive Highlighted Rows” button on the Table tab. All filtering usually has to be turned off to see this button. The macro behind the “Archive Highlighted Rows” button will remove (cut) all Rows with highlighted cells from the Table tab and paste them into the Archive tab. ===== Final Formatting ===== Click the fourth and last “Final Print List” button on the Table tab. This formats the Table for printing, and hides the columns for state, districts, and CBID. If you are printing profiles from several upper or lower legislative districts, it may be necessary to change the Print Title on the printed output. See Appendix – Edit Print Header. ===== Print at Home ===== Go to the ribbon, File, then Print. Print the Table directly to a home printer. The report will be many, perhaps even dozens, of pages long. Let’s hope your ink lasts to the end. ===== Print at Staples, Office Depot, etc. ===== This requires making a PDF and saving it to a memory stick. To do this, you would still go to the ribbon, File, then Print. But this time choose a different printer, a PDF printer. New Windows machines always have a “Microsoft Print to PDF” printer listed. Older machines may have a “CutePDF Writer” or other third-party software. Of course, these aren’t physical printers, but rather software that makes a PDF file from the spreadsheet. When you click Print, a File Explorer dialog box will open asking you to save the file. Choose a filename and a folder to save it to, then click Save. ====== Data Cleaning via the Archive tab ====== One of the benefits of taking rows with errors out of the Table tab so that legislators will see a clean list is that the ARCHIVE tab has all the remaining problem rows. Outside of the pressures of preparing for a visit with legislators, carefully work the data in the Archive tab. Consider using third party tools like Spokeo to resolve difficult address problems. Consider calling the supporter for clarification. Consult with others that do data cleaning. As mentioned above, even with extensive data cleaning, some CB profiles, especially their addresses, may be difficult to fix. It is sometimes impossible with the absence of key data. If you find you do not have permissions to fix some issues (e.g., confirmed account duplicates, banning, deletion), submit a help desk ticket. ====== Acknowledgements ====== Thanks to Rick Raymond for new ideas and direction that made this file much better. Thanks also to Martin Nolan who wrote the code to make the program compatible with Macs. This code also sped up the program by about 3 times. And thanks to JD Pavek for finding many data-related corner cases the program couldn’t handle and for proposing code fixes for them. ====== Appendix – Macros ====== Some corporate environments prevent macros from running via “Group Policy”. The only way around this is to request an exception to the Group Policy. Good Luck. We will assume you are not using the Process Petition List file in this environment! **Simple Fix** {{:cb_mirror_public::media:image8.png}}Usually, all you need to do to enable macros in an Excel xlsm file is to click the “Enable Editing” or “Enable Content” button in the yellow Message Bar at the top of the window, like below: You may have to click one of these buttons for both the petitions.csv file, and the Process Petition List file. This is safe because we know the origins of both files. Do not click one of these buttons if you have any doubt about the security of the file just opened. Clicking one of these buttons will allow you to see **four buttons** at the top of the Table tab, to the right of the data table. If you do not see four buttons, scroll to the right to make sure they are really missing, then try the techniques below. An alternate dialog box you might see when you export the petitions.csv file looks like this: SAVE THE FILE, DO NOT OPEN IT. After saving it THEN open it, with macros enabled. {{:cb_mirror_public::media:image5.png}} The indication that macros have been enabled is presence of **four buttons** in the top right part of the Table tab!! **Enable Macros in the Trust Center** If the yellow Message Bar above doesn’t appear, or clicking a button in it doesn’t work, then go to File, Info and “Enable Content” to enable macros: {{:cb_mirror_public::media:image17.png}} **Trust Center Settings** If this does not work, then we need to make sure that macros are enabled in Trust Center Settings. Click on File, then in the left navigation pane, click “Options.” {{:cb_mirror_public::media:image18.png}} Click “Trust Center” in left navigation pane. {{:cb_mirror_public::media:image19.png}} Click on “Trust Center Settings” {{:cb_mirror_public::media:image20.png}} Above is the “Trust Center Settings” window. Click on “Macro Settings” in the left navigation pane. Your settings should match what is shown above. If you see an option like this: “Enable **Excel 4.0 macros** when VBA macros are enabled” **– Uncheck it!!!** **Last Resort for Enabling Macros** If nothing above works, try one more option in Trust Center Settings. In the left navigation pane, go to Trusted Locations: {{:cb_mirror_public::media:image21.png}} * Add a Trusted Location (folder) by clicking the “Add new location…” button. * The location should be on your physical computer, not on a network or in the cloud. * Here is a typical location: C:\Users\[your username]\Documents\COS When you open File Explorer and go to “This PC”, (C:), C:\Users, “your username” will be pretty obvious. It’s often the only username shown. * Any excel file you place at that location will be trusted, meaning macros will be enabled. **One More Thing** If you use MalwareBytes, you will need to exempt Excel macros from its protection. ====== Appendix – Edit Print Header ====== If the print header doesn’t match the data, for example if the data is from multiple upper and lower districts, then it will be necessary to edit the header on the print-out. First click on Page Layout in the ribbon {{:cb_mirror_public::media:image22.png}} Then click on Print Titles {{:cb_mirror_public::media:image23.png}} This is the **Page Setup** dialog box. This dialog can also be reached via File, Print, then at the bottom of the Print pane click “Page Setup” Click on Header/Footer {{:cb_mirror_public::media:image24.png}} Then click on Custom Header… {{:cb_mirror_public::media:image25.png}} Click in the Center section to edit the text {{:cb_mirror_public::media:image26.png}} ====== Appendix – One-Page Instructions ====== {{:cb_mirror_public::media:image27.png}} ====== Appendix – Developer Notes ====== The program described in this guide is Excel-based, thus using Visual Basic for Applications (VBA), the native macro language in Microsoft Office. The macros in Process_Petition_List will not work in other spreadsheet programs. **Google Sheets** Google Sheets’ macro language is Java. It is a very different language than VBA or any BASIC variant. A complete re-write would be required to convert the macros in Process_Petition_List to work in Google Sheets. **OpenOffice and LibreOffice** These suites use something called CalcBasic as their macro language. They are similar to VBA, not exactly the same. There are tools that aid the conversion from VBA to CalcBasic, but tweaking is required. **Apple Numbers** Numbers can be scripted with AppleScript or JavaScript for automation, but they are not built-in. In any case, their scripting is not compatible with VBA. **Quatro Pro (WordPerfect suite from Corel)** Excel no longer supports importing Quatro Pro files. Only Excel 2003 and earlier versions can import a Quatro Pro file. The Quatro Pro macro language bears no resemblance to either BASIC or Java. **Cleaning profiles is cleaning natural language** This program’s development is never really done, because a human can and will input unexpected text the program has not seen before. For example, we found several supporters with the last name of “True”. Their last name was interpreted as the Boolean value True by the “smart” CSV import Excel process. We found a work-around. We often find problems with character combinations that, if fixed, would create problems with other character combinations. Nonetheless, you may find interesting false positives or false negatives we haven’t seen before. Please submit a help desk ticket (https://help.conventionofstates.com) so we can investigate. Attach your petitions.csv file to the help desk ticket.