Boost Your Excel Skills with VBA: Tips for Outstanding Results!

With this VBA code in Excel, you can easily calculate the average scores for each player from different evaluators. Remove duplicates, run the macro, and sort to find the top players for your team. It’s as simple as that! Check out the code in the video description. Give it a try! πŸ‘πŸ“ŠπŸ₯‡


In this tutorial, we will explore how to utilize VBA in Excel to calculate average results by individual. Recently, I worked with a sports team that needed to evaluate players across three different skill levels – speed, stick skills, and game play – by up to 10 different evaluators. We will go through the process of selecting the names, removing duplicates, and finally, running a piece of code to calculate the average scores for each player.

Selecting and Removing Duplicates πŸ’»

We begin by selecting all the names in the list and removing any duplicates. This can be done by using the "Control + Shift + Down Arrow" shortcut to select all the names, then pressing "Control + C" to copy. Once we’ve copied the names, we go back to the top, paste the names, and proceed to remove the duplicates by going to the "Data" tab and selecting "Remove Duplicates". This will ensure that each name appears only once in the list, simplifying the subsequent calculation process.

"Remove duplicates" – Full Name (2400 duplicates removed)

Calculating Average Scores πŸ“

Next, we run a piece of code that will find each name and calculate the average of their evaluations. We navigate to the "Developer" tab, select "Macros", and run the "Average Scores" macro to obtain the averages for each player.

Sorting Top Players πŸ†

If you’re looking to identify the top players for your team, the process involves selecting columns F, G, H, I, and J and then sorting the data based on the average scores in column J. We sort from the largest to the smallest and this will give us a list of top players, sorted in order of their average evaluation scores.

"OK" – Top player list sorted

The Principle Behind the Code 🧩

The underlying principle of the provided VBA code involves two main sets of rows – one for all the data and the other for individual names. By separating the data into these two sets of rows, the code simplifies the process of calculating and averaging the scores for each player. This involves identifying the columns containing the specific skill levels, looping through all the rows to find matches, and finally, calculating and inputting the average scores in the respective columns.

Code Inclusion πŸ“ƒ

The code demonstrated in this tutorial will be included in the description of the video for your reference. If you found this tutorial helpful, please subscribe for more content.


Key Takeaways πŸš€

  • Utilize VBA in Excel to efficiently calculate average scores by individual.
  • The "Remove Duplicates" feature in Excel simplifies the data preparation process.
  • Sorting data allows for quick identification of top performers.

FAQ ❓

Q: Can this method be used for different types of evaluations?
A: Yes, the VBA code demonstrated here can be adapted for various evaluation scenarios.

Q: Is it possible to automate this process further?
A: Yes, advanced VBA programming can automate the entire process from data input to result output.


Remember, with the right tools and methods, evaluating and identifying top performers becomes a streamlined process in Excel. Happy calculating! πŸ“Š

About the Author

Barb Henderson
34.7K subscribers

About the Channel:

Provide business solutions from Excel. I provide real world uses for Excel. Creating Schedules, Sending Emails and using VBA to get the most out of Excel. I provide Code with most of my videos that use VBA. Answers to your Questions in Excel with “Excel and VBA tips” “tricks and code” I have taught Excel and VBA for a long time and this channel allows me to share “Excel and VBA tips” “tricks and code”
Share the Post:
en_GBEN_GB