Orange County SOE Voter File Formulas

Overview

To filter a voter file from the Orange County Supervisor of Elections, use the formulas provided below. The first formula filters for a specific election by date. Additional formulas are also listed to help you filter for voting methods (polls, absentee, or early.)

For example, if you are looking for people who voted in the April 8th, 2014 Orlando municipal general election, your date is "140408" (yymmdd). The formula returns "TRUE" if the individual voted in that election and "FALSE" if they did not.


Formulas

Copy and paste these formulas into the Excel spreadsheet that has your voter file, being sure to insert a new blank column "BN" and rename the column (in cell BN1) with the election date to fit your specific search (e.g. "140408"). Step by step instructions are listed in the section following the formula samples.

Note: These formulas work for where election history is supplied in columns AA2 through BK2. If your voter file uses other columns for election history, adjust accordingly.


Election Date

=OR(AA2=$BN$1,AD2=$BN$1,AG2=$BN$1,AJ2=$BN$1,AM2=$BN$1,AP2=$BN$1,AS2=$BN$1,AV2=$BN$1,AY2=$BN$1,BB2=$BN$1,BE2=$BN$1,BH2=$BN$1,BK2=$BN$1)


Voting Method = Poll

=OR(AND(AA2=$BN$1,AC2=1),AND(AD2=$BN$1,AF2=1),AND(AG2=$BN$1,AI2=1),AND(AJ2=$BN$1,AL2=1),AND(AM2=$BN$1,AO2=1),AND(AP2=$BN$1,AR2=1),AND(AS2=$BN$1,AU2=1)

,AND(AV2=$BN$1,AX2=1),AND(AY2=$BN$1,BA2=1),AND(BB2=$BN$1,BD2=1),AND(BE2=$BN$1,BG2=1),AND(BH2=$BN$1,BJ2=1),AND(BK2=$BN$1,BM2=1))


Voting Method = Absentee

=OR(AND(AA2=$BN$1,AC2=2),AND(AD2=$BN$1,AF2=2),AND(AG2=$BN$1,AI2=2),AND(AJ2=$BN$1,AL2=2),AND(AM2=$BN$1,AO2=2),AND(AP2=$BN$1,AR2=2),AND(AS2=$BN$1,AU2=2)

,AND(AV2=$BN$1,AX2=2),AND(AY2=$BN$1,BA2=2),AND(BB2=$BN$1,BD2=2),AND(BE2=$BN$1,BG2=2),AND(BH2=$BN$1,BJ2=2),AND(BK2=$BN$1,BM2=2))


Voting Method = Early

=OR(AND(AA2=$BN$1,AC2="O"),AND(AD2=$BN$1,AF2="O"),AND(AG2=$BN$1,AI2="O"),AND(AJ2=$BN$1,AL2="O"),AND(AM2=$BN$1,AO2="O"),AND(AP2=$BN$1,AR2="O"),

AND(AS2=$BN$1,AU2="O"),AND(AV2=$BN$1,AX2="O"),AND(AY2=$BN$1,BA2="O"),AND(BB2=$BN$1,BD2="O"),AND(BE2=$BN$1,BG2="O"),AND(BH2=$BN$1,BJ2="O"),AND(BK2=$BN$1,BM2="O"))


Step-By-Step Instructions

Import the SOE Voter File into Excel and format

  1. Open a blank excel spreadsheet
  2. Go to the File Menu -> Open -> Folder containing your voter file -> Click on the SOE Voter File and hit "Open"
  3. Note: If you didn't see the file, change the file type you are trying to open from "Excel File" to "All Files" (Use the drop down menu in the bottom right corner of the window)
  4. Click "Finish" in the window that pops up
  5. Insert a new blank row above your data (Right click on the number "1" labeling row 1 and hit "Insert")
  6. Insert a new/blank column to the left of the FLVRS ID #'s in column "BN" (Scroll to the right until you see column "BN". Right click on "BN" and hit "Insert". All the ID numbers are now column "BO" and column "BN" is blank)
  7. Rename this column using your desired election date (Example: click on Cell "BN1" and type "140408)
  8. If filtering for more than one election, create more columns (Repeat steps 5 and 6)

                

Use the webElect formula to filter

  1. Copy the formula from this document
  2. In your spreadsheet, click on the 2nd cell in your election column (Cell "BN2")
  3. Paste the formula directly into this cell or into the Function Bar (fx) at the top of the screen
  4. Press "Enter". The formula should now display either "TRUE" or "FALSE" in cell "BN2". Either that individual voted in your election or not.
  5. Click once on that cell again so it is outlined, and then double click on the small square in the bottom right corner of the cell. This will copy the formula to the entire column.
  6. If searching for more than one election, repeat these steps for each of your desired election columns

            

Sort the data

  1. In Excel, click the "Data" menu at the top of the screen, then click "Sort" (If it asks what data you want sorted, click "Expand the selection")
  2. In the pop-up window, check the box next to "My data has headers"
  3. Use the drop down menu to change the "Sort by" to the name of your election column, keep "Sort On" at "Values", and change the "Order" to "Largest to Smallest" (or use a FALSE/TRUE custom list if you know how to do that).
  4. Your spreadsheet should now be sorted with all individuals who voted in your election (all "TRUE"s) listed first and those who didn't ("FALSE"s) last

            

Upload the data into webElect

  1. In webElect, create a new Group (Example: "Orlando Municipal Voters". Read more about webElect Groups.)
  2. On the Group List page, click on the Action button "Import" in the row corresponding to your desired group.
  3. In your spreadsheet, highlight all of the voter ID #'s corresponding to the individuals who voted in your election (all the ID #'s numbers next to a "TRUE")
  4. Copy and paste those ID #'s into webElect in the empty field that says "Voter ID Numbers"
  5. Click "Save" underneath this field
  6. webElect will now tell you how many voter were added to your group
  7. Click "Ok"


        


Last updated by Jake Friedman, created November 20, 2015