Revealed: the answer and winner for last month's competition
Last month's competition asked you to reveal the hidden question. We had 24 entries nominating their favourite films - congratulations to Victoria Anthony of Sucden Financial for being the first name pulled out of the Wise Owl sorting hat!

Posted by Andy Brown on 25 November 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

The winner and solution for our October 2019 competition

The winner and solution for last month's competition are shown below, along with various other interesting snippets of information!

The problem

Last month we asked you to find the missing question:

The missing phrase

The task was to find out what the missing question was.

I inadvertently used the CONCAT function, which I think only came in to Excel in version 2016 - apologies that this made life harder for a few people.

Fortunately, we left instructions:

Instructions for what to do

There were only 2,293 steps to follow ...

 

The answer

We received a record response of 24 (all correct) answers.  The missing question was:

What is your favourite film?

I think most people used text-to-import or Power Query to extract the numbers from each question, then used filters, subtotals or pivot tables to get the grand totals for each of the 25 boxes (I used VBA instead).

The winner

Congratulations to Victoria Anthony of Sucden Financial, whose name was picked out of the Wise Owl electronic sorting hat, and who receives this month's £50 Amazon voucher.

I used Excel to type the formula =RAND() against each name, pressed F9 to recalculate the worksheet 10 times, copied/pasted values and then sorted.  Fairly random ...

The films chosen

Here are the 24 films chosen:

24 most popular films

Shawshank Redemption is the only film which was chosen more than once, justifying its place at the top of IMDB's Best Movies Ever list.

 

I toyed with the idea of awarding the prize to the person who chose the best film (in which case The Sound of Music contributor would surely have won).

Another solution

For those who don't want to have to open another file, here's Andy (Gould)'s suggested code to solve the problem:

Option Explicit

Sub FollowInstructions()

Dim Instructions As Range

Dim Instruction As Range

Dim NumberRange As Range

Dim NumberArray

Dim NumberToChange As Integer

Dim NumberToAdd As Integer

Set NumberRange = Worksheets("Phrase").Range("B4:AC4")

NumberRange.Value = 0

NumberArray = NumberRange

Set Instructions = Worksheets("Instructions").Range("A2", Worksheets("Instructions").Range("A1").End(xlDown))

For Each Instruction In Instructions

NumberToChange = CellNumber(Instruction.Text)

NumberToAdd = ValueToAdd(Instruction.Text)

NumberArray(1, NumberToChange) = NumberArray(1, NumberToChange) + NumberToAdd

Next Instruction

NumberRange = NumberArray

End Sub

Function CellNumber(InstructionText As String) As Integer

CellNumber = Split(InstructionText, " ")(5)

End Function

Function ValueToAdd(InstructionText As String) As Integer

ValueToAdd = Split(InstructionText, " ")(1)

End Function

This is a better solution than mine, but be aware that you'll also need to know more VBA to make sense of it!

This blog has 0 threads Add post