Automatic generation of input data

dekion

New Member
Joined
Sep 8, 2017
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi

A friend of mine asked me if I could help him around with following case.

He's in a card club and they have about + 30 members.

Members join beginning of January and play for a complete year. The play once a month cards together and he notes down all the scores. Every month they rank the best player and the person who wins the most is at the end the champion.

Now I can easily create a sheet that does following:

NameMonthGames wonPoints
Johnjan3450
Willjan2400
Gloriajan1360
Johnfeb1200
Willjan1320
............
Johndec1100

But I would like to avoid to recreate every year this table and I'm trying to find a way to automate all the participants and date to automatically create beginning of the year.
So he only needs to maintain 1 list of participants and automatically everything gets generated, but I wan't to avoid VBA.

The person isn't a Excel expert, so I really want to keep it simple, but I'm lost in my design idea.

Any ideas how I could start with this?

Off course I'll add validation and list where possible so the error margin is reduced to almost none ;-)

Thank you for the feedback
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
I don't have all of the details of what you want to accomplish, but in my mind there are two main approaches: Power Query or a combination of Spilled/Dynamic formulas. In both cases, you would want to make sure your data is a structured Table.

Power Query would be nice because you could use the GroupBy feature. If you need to award points for the top scorers by month, you could end up doing some fun things with Index columns. The nice part about the Power Query approach is you create the steps and all that the friend has to do is refresh after data is entered.

If you want the formula approach, I would end by using the UNIQUE and SORTBY functions and do a number of SUMIFS, utilizing the # operator in your criteria.

If neither one of those suggestions make sense, let me know.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top