A variety of answers to last month's Treasure Island Excel competition

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

A variety of answers to last month's Treasure Island Excel competition
In computing it's enough to solve a problem, but as a bonus it's nice to do so in the most elegant way (although elegance is a subjective quality). This blog gives my 3 favourite ways to solve last month's Excel desert island competition.

Posted by Andy Brown on 20 June 2024

In this blog

Answer 1 - Using Excel
Answer 2 - Using VBA
Answer 3 - Using Other Software

For those who have forgotten, you can see (and try) last month's competition here. As a quick reminder, you were presented with two worksheets:

The first worksheet contains the text of the answer, the second contains the position number of each letter within this (so for example cell A1 tells you that u is the 404th letter in the answer).

There are lots of ways to solve this (many people used some combination of INDEX/MATCH or XLOOKUP to lookup the letter at a given position in a set, but these answers tended to be quite complicated). I've picked out what I think are the 3 nicest solutions below.

Answer 1 - Using Excel

The first thing I did was to create two ranges called Letters and Numbers:

The Letters range (the Numbers range was similar for the block of numbers on the second sheet).

You don't have to do this, and it doesn't make any difference to the solution given below, but range names always make formulae easier to read.

Excel has a new(ish) dynamic array SORTBY function, which allows you to sort one range of data by another - ideal for what we're trying to do. However, this only works on rows or columns of data, not on two-dimensional ranges. So the first thing to do is use another new(ish) function TOROW to collapse the data into a single row (I could have used TOCOL to collapse the data into a single column instead):

This creates a dynamic array of (as it happens) 841 cells.

You can now repeat this for the numbers:

This does the same thing for the numbers.

Now you just need to sort the letters by the numbers:

The # symbol tells us that we should refer to the dynamic arrays created for rows 1 and 2 beginning in column B in each case.

You can now join everything together:

This joins the cells together, ignoring any empty ones (there aren't any) and using an empty string as the mortar to join each two cells' contents together.

You can now read the final message! After I tipped Simon Borland off about this approach, he pointed out that you can take it one step further and combine everything into a single formula!

=TEXTJOIN("",TRUE,SORTBY(TOCOL('The message'!A1:AC29,0,0),TOCOL('The order of letters'!A1:AC29,0,0)))

This solution (using cell references rather than range names) gives the answer in a single step.

Answer 2 - Using VBA

Martin Hughes's solution to this problem was beautifully elegant I thought (I've added some comments to his 8-line answer):

Public Sub SortLetters()

'make sure your array is bigger than the number of letters

Dim answer(1000)

'variable to refer to each cell containing a letter

Dim LetterCell As Range

'for each of the letters ...

For Each LetterCell In Sheet1.Range("A1:AC29")

'set this element of our array to be the letter (eg for cell $A$1, the

'corresponding order number is 404, so set answer(403) - arrays being

'0-based - to hold this letter.

answer(Sheet2.Range(LetterCell.Address).Value - 1) = LetterCell.Value

'join all of the entered results

MsgBox Join(answer, "")

End Sub

Running this gives the answer:

The answer is easier to read than in the Excel cell equivalent.

I honestly don't think you could improve much upon this VBA program.

Answer 3 - Using Other Software

No one said that you had to solve this using Excel! Ed Boughton, for example, transformed the data using Alteryx:

The Alteryx flow.

I'm sure you could do the same thing using Power Query transforms, although not convinced that it would be as simple as the answer shown above.

Many thanks to everyone who entered the competition. All correct answers were accepted!

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Answer 1 - Using Excel

Answer 2 - Using VBA

Answer 3 - Using Other Software

Head office

London

Manchester