Two weeks ago I outlined how a Double Dissolution election would pan out using Above the Line votes from Queensland. The analysis concluded that there would be five LNP senators; three from Labor; and one each from Palmer United, the Greens and the Fishing & Lifestyle Party.
However, this excludes the votes of the 78528 people — including myself — who voted below the line.
When I started trying to transform the data provided by the Australian Electoral Commission (AEC) I thought it would take me about a month to complete. This was by going ballot to ballot to see if and where the particular ballot ceased being counted and remove subsequent preferences.
Last night however, I found a spreadsheet formula that I ended up using to speed the process right up. The formula — which I used for all bar the first preference of each ballot — being something like
=IF(AND(B7B8,F6""),A7,""). This particular formula is for preference 7 of ballot 10 of batch 367.