WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 527 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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 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:

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

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

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:

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

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)

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