Challenges: Monday Morning Mulling: January 2021 Challenge
1 February 2021
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 first Final Friday Fix challenge of the year is to generate the list of the five most recent items using Excel formulae.
The Challenge
There is an Order_Database data set which contains details of Customer Name, Order Date and Order Volume over the time as shown below.
To facilitate the conversation with customers upon placing an order, the operator wants to view the five most recent orders from a given customer. Specifically, when a customer is selected from the list in cell F140 (see image below), their last five order dates will appear in cells F146:F150 in descending order, together with the related order volumes.
Can you get the list in cells F146:F150 in just two steps?
Suggested Solution
Step 1: Create a Dynamic Range Name using a formula
Navigate to the Formulas tab on the Ribbon and select ‘Define Name’. In the ‘New Name’ dialog, the List_Last_Five_Orders range name should be created using the formula below:
=SORT(UNIQUE(FILTER(Order_Database[Order Date],Order_Database[Customer Name]='The Challenge'!$F$140)),,-1)
Yes, we are using the >dynamic array formulae to make it work, so we understand this is a solution for Office 365. However, we should avail ourselves of all the features available in modern Excel.
Let’s break the formula into digestible pieces to understand how it works:
- the FILTER function will get the list of all dates related to the customer selected in cell F140
- a customer may make multiple orders in a day, so the UNIQUE function will return no duplicates in the list of dates
- therefore, the SORT function will arrange this list based upon descending order (as seeing the most recent order first probably makes the most sense).
Step 2: Get the list of last five order dates using the >INDEX function
Given the List_Order_Date, the cells F146:F150 may be filled using the INDEX function. In case the list of historical order date related to the customer is less than five (5), the formula should return a blank, viz.
=IFERROR(INDEX(List_Order_Date,E146),"")
Hence, the order volume can be calculated using the SUMIFS function.
You can take a look at our solution using the attached file. Please share with us if you have a better solution. We won’t let anyone know, but we will steal it for the future :)
The Final Friday Fix will return on Friday 26 February 2021 with a new Excel Challenge. In the meantime, have a great new year and please look out for the Daily Excel Tip on our home page and watch out for a new blog every business workday.
