Final Friday Fix: August 2023 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI 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.

The Challenge

Given a table of sales data, how do you find out the favourite store location of each customer?  You might wish to filter, then count and then sort.  Alternatively, you may try opening Power Query to perform some advanced manipulations.  However, this month, we challenge you to achieve the goal with only one [1] Excel formula!  You can download the question file here which asks you to identify the store most commonly visited by each customer.

Using the same data, the second part of the challenge is to find, for each customer, the location of the last store they visited, again with only one [1] Excel formula.  The table Data contains the customer visits data (pictured), and the desired outputs should look like the following upon completion:

As always, there are some requirements:

• each formula needs to be within one cell
• this is a formula challenge; no Power Query / Get & Transform or VBA.

Feel free to use the attached Excel file to assist you.

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