I’ve just extracted the segments of Oz du Soleil‘s BBC Radio 5 Live interview broadcast today (after frantically searching for them).
Du Soleil was on in recognition of All-Star Battle, the hugely successful Excel esports tournament featured on ESPN2.
He set co-presenter Rick Edwards an Excel challenge to complete during the show.
Edwards was faced with a worksheet containing a 6 x 12 grid of 72 names. Most repeated — a few didn’t.
The task was to extract the unique names and sort them alphabetically.
His solution was to apply conditional formatting to the range, so the non-repeating entries were coloured differently.
Edwards correctly identified the four: Alec, Michelle, Moe, and Ryan. Du Soleil then instructed Edwards to insert a new name into the grid — ‘Horris’ — to see if the list updated automatically.
Edwards admitted he had manually written the names on the side.
Du Soleil proceeded to explain his answer.
He advised using TOCOL to transform the array into a single column. This statement would then sit in UNIQUE, with [𝚎𝚡𝚊𝚌𝚝𝚕𝚢_𝚘𝚗𝚌𝚎] set to 1 so only the unique names return. Finally, wrapping this all in SORT would mean the names output in ascending order.
Example formula: =𝚂𝙾𝚁𝚃(𝚄𝙽𝙸𝚀𝚄𝙴(𝚃𝙾𝙲𝙾𝙻(𝙱𝟸:𝙼𝟽),,𝟷)).
Hats off to Rick Edwards for being a good sport, but as he confessed: “I’ve massively struggled.”
Well worth a listen.