Challenges: Monday Morning Mulling: April 2019 Challenge

On the final Friday of each month, we set an Excel problem for you to puzzle over for the weekend. On the Monday, we publish a solution. If you think there is an alternative answer, feel free to email us. We’ll feel free to ignore you.

Here we have a column called “Code” which has text and numbers. We want to get rid of the numbers and replace them with the section headings. Let’s pull it into Power Query. I will use this as an example:

First, I replicate the column “Code”:

I move the duplicate column to the left:

I now convert the data type of the duplicate column to data type “Whole Number” (say):

All text appears as “Error”. I can’t replace errors yet as the data type is a number not text. So I change the data type back to text first:

Now I can replace errors without an issue:

I use something that I know won’t be in the column:

It doesn’t have to be “Liam007”!! Now we have to replace “Liam007” with the corresponding value in the Code column. Because this varies, we CANNOT use ‘Replace Values…’. Instead, we have to go to the ‘Add Column’ tab and click on ‘Conditional Column’:

I then do the following:

Ensure the icon under ‘Output’ is ‘Select a column’ not ‘Enter a value’ otherwise you will get the same value throughout. This gives you:

I delete the first column (Code-Copy), rename ‘Custom’ as ‘Classification’ and move it to the left, viz.

I then Fill Down on the first column:

Voila!

You can then filter as you see fit. For example:

This may now be loaded into Excel:

Yay! Did you find a more elegant solution?

The Final Friday Fix will return on Friday 31
May with a new Excel Challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business workday.