A medium sized tupperware box, 11 x 8 inches i(pictured), works for a ward level election where there might be 3 or 4 ballot boxes.

Two large storage boxes, 21 x 16 inches, should work for constituency elections where there might be 60 or so ballot boxes, or 120 or so boxes if two elections are run in parallel (for example Police and Crime Commissioner and local elections).

The use of translucent boxes allow onlookers to see the boxes are empty at the start, and for counting staff to see where to put the seals.   

The approach outlined above results in a jumble of seals not matched with ballot boxes.  There will be between 3 an 5 official seals per ballot box, making between 9 and 20 seals for a ward election, 180 to 300 seals for a constituency.  Any candidates' seals will add to these numbers.

Verification

Verifying the 10 to 20 seal numbers for a single ward election can be done by looking for each number in the notes made at polling stations.

For a constituency level election, with 180 to 300 seals, there is a need to sort the seal numbers into order. Here are some approaches:

Sorting numbers using spreadsheets

Some considerations:

• do you want to take a laptop, tablet or mobile phone  to the count?

• will you have mains power and wifi at the count? (safest to assume not, power and wifi can be turned off)

• can you / do you want to print a sorted list of seal numbers and take that to the count?

• Logistics. Is the count straight after the poll. or on a day after the poll?

Spreadsheet Programs and Apps

We cover the following systems:

 Simple Spreadsheet Approach

1. Enter the seal numbers into column A

2. When finished, sort the column:

for Microsoft Excel: click on the A at the top of column A to select the whole column. From the menu, select Data / Sort Smallest to to Largest  

for  Apple Numbers desktop: click on the A at the top of column A. In the right-hand sidebar, click on Sort & Filter. Click Pick Column A. Click Ascending

for  Apple Numbers mobile: tap on the A at the top of column A. Tap the paint brush icon (format menu). Scroll down and tap Sort. Tap Sort Ascending.. 

for Google Sheets  desktop: click on the A at the top of column A to select the whole column. From the menu, select Data / Sort Sheet / Sort Sheet A --> Z

for Google Sheets mobile:  tap on the A at the top of column A. Tap the three dot menu. Tap Sort A --> Z.

You can print the sorted numbers  and take the paper to the count, or take your device.

At the count, sort through the seals making two piles. Mark match numbers off on the list so you will see if you are going to mark the same number twice.

If you don't use spreadsheets much,  install the Google Sheets App for mobile and follow the procedure above. Also tap the three dots menu and turn on 'Available offline', so you don't depend on mobile data or wifi.

A Simple Google Sheets Spreadsheet Macro

With the help of ChatGPT, we've created a Google Sheet with a macro to sort column A and put a tickbox in column B. Unfortunately, macros are disabled in the Google Sheets App for mobile. It works for desktop. The macro is not verified so there's a 4 or 5 step dialogue to run it as an unsafe app. We will try a little bit to get it verified with Google but it has limited usefulness (we need a custom data capture app really).  

The spreadsheet is here:

https://docs.google.com/spreadsheets/d/1KIbzVYEll3FZzm3l-1S1MuFIXinPXO8hKGpR6S667dI/edit?usp=sharing

It opens in 'View only' mode, you need to make a copy to be be able to edit.

This spreadsheet does not work on mobile.

Using the spreadsheet Match function

The match function works on Excel, Numbers and Google Sheets. It looks for a number in one cell in a range of cells and can be setup to give the row number of the match.

Both sets of numbers much be entered, twice the work, and can mean data entry at the count, or you could use this approach after the count.

The basic idea is to have two lists of ballot box seal numbers, say in columns B and C, and to have the following formulae in columns D and E:

=MATCH(C3, B$3:B$999, 0)+2

=MATCH(B3, C$3:C$999, 0)+2

The formulae ignore the first two rows, which can be used for headings, which is why they start at row 3 and add 2 to the result to give absolute row number.

Here is are demo of this approach using Google Sheets:  Box Seal Reconciliation using Match function demo.  You will have to make a copy (File / Make a copy) to be able to edit the numbers.  It can be downloaded as an XLS file:

              this can still be be done during the count,  as the 6 digit ballot seal numbers will run in sequences.  Here is one approach:

• before the count, look through the seal numbers you noted at polling stations and make a note how many different first three digits there are (for seal number 773125 the first three digits are 773).  Prepare an A5 size notebook for each first digit. The notebooks must have at least 100 pages. Write the first digits on the front covers of each notebook. For each book, number the top right of each page from 1 to 99, writing the first 9 pages with a leading zero, '01', '02' etc.

• For each physical ballot box seal, open the book that matches the first thee digits and turn to the page that matches the fourth and fifth digit (for seal number 773125 that would be book 773 page 12). Write final 6th digit on that page, writing 1 at the top of the page and 9 at the bottom, so the numbers are written in sequence.

• Process the other seals in the same way. When you have  a new first 3 digits, start a new book by writing the first 3 digits on the front cover.

• When finished, the books can be used to lookup ballot seal numbers that were recorded at polling stations. Mark the A5 book pages with a tick by each number that is matched, to prevent the same number being matched twice and to allow unmatched numbers to be found at the end of the process. Also mark numbers recorded at the polling stations with a tick, for the same reasons.

• It would be best to have two people working on this together, one writing and one observing, to reduce errors.

• At the end of the process, make a list of unmatched seal numbers in the A5 books and another list of unmatched numbers note at the polling stations. Compare the two  lists looking for common transcription errors, which are 

Spreadsheet Verification  

You can use the MATCH function an Excel, Google Sheets,  or Libre Office spreadsheet:

• use the firt two rows for headings, start of row 3

• enter the polling station number and seal numbers recorded at polling stations in the first two columns. 

• leave the third column empty for now

• at the top of the fourth column, add the formula: =MATCH(C1, B$3:B$999, 0)+2

• copy and paste the formula to the rest of the fourth column's cells. All of column 4 will show '#N/A'

• enter the spent seal numbers into the third column.  As each seal number is entered, column 4 will show the lineof the matching seal, or '#N/A' the seal does not match. Set unmatched seals to one side.

• when finished, add the following formula to the fifth column: =MATCH(B3, C$3:C$999, 0)+2. This will show unmatched numbers in the second column (seal numbers recorded at poling stations).

• page through looking for '#N/A' in column five. When found, compare the number in the second column with the the unmatched seals you set aside, looking for transcription errors, such as transposed digits or misread digits. Make any corrections to the number in the second column and make a note of what you've done in your own notes.

Use the App

As off-the-shelf website such as CompareTwoLists.com, ListDiff.com, and OCR  apps don't have the necessary checks and workflow built-in, we need our own webpage and ideally an app.

Webpage:  Two lists of 300 numbers is manageable for the user. I can do the code in-house.

App:  I've never written one, and could do with help. Here's the spec:

• create records in a flat file  csv format, redundant data such as 'location' is present in all records and to the left. 

• create an observer file, including observer name, associated candidate name, election name, location and country name, and an email address for csv file reporting

• create polling station records, including the following:

  • record polling station name and number

  • include date, time, and GPS derived longitude and latitude for each seal number

  • each polling station has one or more ballot boxes which have multiple seals. The dialogue should reflect this.

  • use a 3 x 4 large telephone style numeric keypad for inputting seal numbers

  • when 6 digitsseal number  has been entered, read the number aloud, ask for confirmation and allow corrections

  • when finished with the ballot box, ask if there is a second person to record the seal numbers for a second time, for the elimination of errors. If yes, repeat the process. Have a first observer, second observer field in the csv  record. Record the initials of the second person.

• create a seal number verification dialogue that can be used to process ballot seals removed from ballot box opened for counting (At The Count)

  • use a 3 x 4 large telephone style numeric keypad for inputting seal numbers

  • match the number entered against the polling station list for all polling stations. Display a message saying good or bad match. Ask for bad match seals to be set to one side.

  • at the end of the process, create a discrepancy report in csv and/or pdf format and email it to the email address provided by the observer.

  •  present lists of unmatched polling station records and count records to the use and allow them to pair likely data entry errors. Email the result to the email address provided by the observer.

  • give overall summary numbers to the user.    

I'm thinking about a Partisan Observer custom app, ideally Second best would be manually entering seal numbers. The app could/would also look for gaps in the seal numbers and for near matches based on common transcription or OCR errors. It should have language support for countries that use numbered ballot box seals: UK, USA (include Spanish), Canada (include French), Australia, New Zealand, India, Bangladesh, Singapore, Russia, Belarus, Czechia, Slovakia, Bulgaria, Romania, Moldova, the Baltics, the Turkic speaking 'stans.