Need assistance with automating spreadsheet

[RatedX]

Hi,

I have a new role as a stats keeper for a sports competition I am in. At the end of a season, all 60 staff vote on and rank their top 20 players (out of around 250+)

The vote system is like this. Each staff member picks their top 20.

Top player - 40 points
Second - 37
Third - 35

Until the 20th best, 1 point

Etc

It is very time consuming, the current spreadsheet set up has every voter in columns and the players (only players who receive votes) on the left,. Currently, its all entered manually and making mistakes, including doubling up on players is common.

How can i automate this? I am no excel expert, only know basic formulas. I would like to be able to cop/paste votes into the spreadsheet and let excel do the rest of the work.

[Pete_UK]

We can't recommend any formulae without seeing your data. Please attach a sample Excel workbook, by following the instructions given in the yellow banner at the top of the screen.

Hope this helps.

Pete

[FDibbins]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[RatedX]

Hi,

I have edited the thread title and added a sample workbook. Sorry if my request seems vague or generic, if I had more knowledge about functions, etc, I would go into more detail. What I am looking for is a push in the right direction of what function I could use to automate this process, I can then look up how to use these. Sorry if I am not clear

[Pete_UK]

I think you need to describe your processes in a bit more detail. From what you have said, you have 60 members of staff who each vote for their top-20 players from a list of 250. How do they get their voting slips to you, and what do the slips look like? Do they record 20 names in order, and then send you an Excel file? Do you then copy/paste each of those 60 files into one sheet of an Excel workbook, or do you have one sheet for each member of staff?

Your sample file does not show the voting process, so we still need more from you.

Pete

[RatedX]

I think you need to describe your processes in a bit more detail. From what you have said, you have 60 members of staff who each vote for their top-20 players from a list of 250. How do they get their voting slips to you, and what do the slips look like? Do they record 20 names in order, and then send you an Excel file? Do you then copy/paste each of those 60 files into one sheet of an Excel workbook, or do you have one sheet for each member of staff?

Your sample file does not show the voting process, so we still need more from you.

Pete

They send it in email form using the following format:

1. Player1
2. Player2

Etc etc

Player1 1 is allocated 40 points, player2 37. Then it decrements by 2 until you get to the final player (1 point)

So i copy and paste the players into the Players column, ensure there is no player double ups, and update the voters column with the points (40 for player1 etc).

So currently, players are copy and pasted then the votes I enter manually. The totals are automated, but i am wondering if I can automate the voting process.

[Gregb11]

Here is my suggestion based on my understanding. Create a sheet that lists all the Staff and as they send in their picks (list of names), just copy the names under their names. Have a column to the left that shows the points for each pick. On your main page, enter this formula in cell F4:
=IFERROR(INDEX(Sheet1!$A$2:$A$21,MATCH(MVP!$B4,INDEX(Sheet1!$B$2:$F$21,,MATCH(F$3,Sheet1!$B$1:$F$1,0)),0)),"")
You can then copy this all the over and all the way down.
As you copy in new picks, the points should update.
Please see attached.

[Croweater]

You have 60 'staff' arranged somehow in 12 teams of 5. Is that correct?

With 60 people just sending in names on a spreadsheet you are going to have a big variation on accuracy, unless you have some sort of validation.

Names will be misspelled, some will be missing, some in twice, etc. etc. which will be/is a nightmare for you.

So my suggestion is to put some responsibility on your staff to get it right. Make them do most of the validation work...that is why they are called 'staff' ;-)

This means giving them a spreadsheet with all the names on it and make sure every one of the 20 they select is one of the 250 and there are no duplicates.
This can be done with a dropdown of course but picking a name out of 250, 20 times will be a bit painful, so I'd make it a searchable dropdown which puts a selected name in the next slot.

Obviously you'll need to allow them to change and manipulate their list in case they get it wrong, but that is quite easy to do.
Also quite easy to highlight duplicates.

You can also get fancy by an automatic mail direct to you.

The important thing is that you will (eventually) have 60 valid lists for you to process.

Then it would be (relatively) easy to dump all your 60 (valid) workbooks in a folder and have your master workbook go through each one, add up the votes and sort it into order. Game Over for you with no work apart from a button press!

Of course there will be some work up front which I would be prepared to help you with as we are now in hard lockdown.
That is of course if the above suggestions don't work for you, because what I am not prepared to do is waste a lot of time with something you don't want or won't use, because you already have a solution.

attached is an idea of what I would give your staff.

If you like my suggestion, the first thing to do is answer the very first question in this rather long winded response.

[Croweater]

OK, I had a bit of time during our lockdown so I thought I'd develop this a bit more.

I have attached two workbooks. The first you (or your staff) enter their team/staff number and your email address.
They then enter their selections from the player list in column F.

They can do this by searching the list and selecting (via a dropdown) and then clicking the slot they want to put it in, by selecting from the whole list, by typing it in or even by copy and paste - doesn't matter.
Once their list is completed, they press the button to validate and send an email to you with their selections as an attachment.

When you get the email, the attachment will have a unique name (team/staff numbers) so you can copy/save this into your MVP folder.

With your award workbook (attached) you specifiy what folder you put the selections in and press the button. You can do this any number of times (e.g. to see how things are going even if you only have half the returns).

You can press the other button to sort by rank.

Good Luck.