Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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. |
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.
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.
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
Next
'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.
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!
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.