BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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!
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).
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).
For those who don't want to have to open another file, here's Andy (Gould)'s suggested code to solve the problem:
Dim Instructions As Range
Dim Instruction As Range
Dim NumberRange As Range
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
NumberRange = NumberArray
Function CellNumber(InstructionText As String) As Integer
CellNumber = Split(InstructionText, " ")(5)
Function ValueToAdd(InstructionText As String) As Integer
ValueToAdd = Split(InstructionText, " ")(1)
This is a better solution than mine, but be aware that you'll also need to know more VBA to make sense of it!