Final Friday Fix: November 2021 Challenge

On the final Friday of each month, we are going to set an Excel / Power BI challenge for you to puzzle over so that you can get your “Excel fix”. Challenge your office colleagues to see who can solve the puzzle quickest. There are no prizes at this stage: you are playing for bragging rights only!

This is based upon a real-life problem, which was not as easy to solve as we thought it would be.

The Challenge

Numbers with leading zeroes may sometimes cause problems. In order to find duplicates from a list, you might think of using FILTER with COUNTIF for example. However, COUNTIF cannot distinguish between numbers with and without leading zeroes, or “real” numbers and numbers which have been stored as text. Surprised? Try it and see!

As you can see below, the number of occurrences of each number is calculated incorrectly by COUNTIF:

Therefore, this challenge is trickier than it might at first seem.  You can download the number list here.

This month’s challenge is to write a formula in one cell using Dynamic Arrays that will spill down to generate a list of duplicates (i.e. all numbers that show up more than once) from the number list in the file above.  The result should be similar to the list generated on the right (below):

As always, there are some requirements:

• the formula needs to be in just one cell (no “helper” cells)
• it should treat these kinds of numbers differently:
  • with and without leading zeroes
  • “real” numbers as opposed to numbers stored or formatted as text
• this is a formula challenge – no Power Query / Get & Transform or VBA!

Sounds easy?  Then why not have a go?  We’ll publish one solution in Monday’s blog.  Have a great weekend in the meantime!