We all aware of harnessing the power of VBA to dynamically sort dashboards based on user input. But is there a simpler, more elegant solution? Yes, there is. And Excel MVP Jordan Goldmeier is here to solve this mystery for us.
Let’s dive in!
1 – The Excel Dashboard Data
The image below shows an illustrative data set, where A, B and C are metrics were are interested in. Also, the Result can be interpreted as an overall score (based on these metrics) representing the effectiveness of each project.
The problem at our hand is to have a sorted version of this table based on what the user chooses from the metrics or the Result.
2 – Setting up the Dashboard
The image below shows the setup we will be using. Of course, you can use any template you like. The important thing is to get an understanding of the technique.
The User Choice is a dropdown menu containing the choices A, B, C and Result. The end-user can choose any of them based on their interests. The Match just returns index of User Choice from the green highlighted set of cells. Note that we will always be using an exact match.
3 – Sorting Column
In this step we will add a column to our data to fetch the information we are interested in. As demonstrated in the image below, we just index the value we are interested in.
Of course, the value to fetch depends on the result of our Match from the Setup above. Since we selected C in our template, for example, the Match was 3. ‘Sorted Data’!$C$3 represents the location of this 3 in our template chosen. Hence, as expected this formula would return 70.0.
4 – LARGE function
Now, in our template, we would be using the LARGE function to sort the data into descending order. The image below shows the input formula.
We selected the array “Sorted Column” from the data, and the LARGE function returned the 1st largest value. Dragging the formula down to Index 2 will give the 2nd largest value, then the 3rd and so on.
5 – MATCH and INDEX
In the Match Index column in our template, we try to find row number of the value returned by the LARGE function from the data. It has been illustrated in the image below.
Once we know this row number, it is easy to use the INDEX function to fetch the Program Name, A, B, C and Result from this row in the data. The result will look something like this:
Notice how the projects are listed in a descending order according to metric C. However, there is one problem: some entries are repeated! Well, this actually was expected. Let’s address this now.
6 – Sorting Column Revisited
As you might have guessed it, the problem occurs because multiple projects can have the same score. And since the MATCH function always picks up the first one, it gets repeated multiple times. Now, we will attempt to change the Sorted Column part of our data to prevent this repetition.
As shown in the picture, we just add the index number divided by 10,000 or 100,000. Since no project has the same index number, no project will have the same number in this column. And now when we turn to our template, we see that the duplicates have vanished!
See how the same sorting template is also working perfectly when Result is selected in User Choice.
This process of adding varying amounts of very small quantities to the data to make every entry unique is known as data jittering. And this technique is commonly employed in graphing problems.
What’s next?
Watch the video if you haven’t yet! Jordan shows some amazing dashboards to demonstrate this technique. Also share it with your friends and colleagues. There’s a lot that can be done through mastering these lessons.
And do not forget to share your thoughts and experiences with us in the comments section below. Cheers!
- SSSVEDA DAY 7 – Every Team Needs Someone Who Understands Data - February 18, 2018
- SSSVEDA DAY 5 – When Data Analysis is Wrong - October 31, 2017
- SSSVEDA DAY 4 – Sharing the Excel Knowledge - July 18, 2017
