Questions on Automatically sorting

**naccoachbob** · 07-11-2017, 02:01 AM

I have a spreadsheet that right now I use to track a FIFA Soccer Tournament (The Gold Cup) being held in the US this month.
There are 12 teams in 3 groups of four. The top 2 in each group will advance from the group stage, and 2 of the third place teams will as well.
Teams get 3 points for a win, 1 for a tie, and 0 for a loss in each match they play. All teams play 3 matches.
So teams are first sorted in descending order of the points they have.
Next they are sorted by goal differential (goals scored for a team - the goals scored against).
The next criteria is the number of goals each team scores.
There are other tie-breakers, but they're not needed right now.

Is there any way I can get Excel 2016 to Automatically sort the teams in each group when I enter their scores in another area?
I will need to do the same thing for all 3 groups. It's just getting over the hump of making the sort become automatic.
Thanks in advance for any help.
Bob

Edit: I added an attachment in the #4 post on this thread. Thanks all.

**AliGW** · 07-11-2017, 02:15 AM

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

**FDibbins** · 07-11-2017, 02:21 AM

1 way would be to use the macro recorder. Activate it (bottom left, next to the Num Lock icon), and (carefully) go through the steps you would follow to do this manually. When complete, turn off the recorder and you now have a macro do do the heavy lifting for you.

To use it, put in (any) shape in the sheet, and link the macro to it. Then just push that "button" and it will repeat all the steps you did

**naccoachbob** · 07-11-2017, 10:59 AM

I use the Schedule tab to enter scores from each match. That info goes to each Group tab, and all the formulas in those tabs populate the cells for W, D, L, GF, GA, +/-, Points. Then, the total of each of those cells goes to the Standings tab.
At that tab, I have to manually highlight the cells (For Group A - cells B6-I10), and sort from larger to smaller on Pts (Points), then on +/- (Goal Differential), then GF (Goals For). The other cells are just informative, and don't come into play until later on, if even then.
I would like to find a way so that when I enter scores in the Schedule tab, that each group in the Standings tab will automatically sort to the criteria I've used.
If there's anything I failed to do, please let me know. I'm new to this forum.

**naccoachbob** · 07-11-2017, 06:36 PM

Originally Posted by FDibbins

1 way would be to use the macro recorder. Activate it (bottom left, next to the Num Lock icon), and (carefully) go through the steps you would follow to do this manually. When complete, turn off the recorder and you now have a macro do do the heavy lifting for you.

To use it, put in (any) shape in the sheet, and link the macro to it. Then just push that "button" and it will repeat all the steps you did

FDibbins, thank you for the suggestion. I've never worked with macros, but I'm going to learn a little bit about it in the next days, and give it a shot.
Much appreciated,
Bob

**naccoachbob** · 07-12-2017, 05:49 AM

Originally Posted by FDibbins

1 way would be to use the macro recorder. Activate it (bottom left, next to the Num Lock icon), and (carefully) go through the steps you would follow to do this manually. When complete, turn off the recorder and you now have a macro do do the heavy lifting for you.

To use it, put in (any) shape in the sheet, and link the macro to it. Then just push that "button" and it will repeat all the steps you did

I figured out how to Record a Macro, and after some growing pains with it, I sorted it out. I didn't know that I shouldn't use a mouse if I could help it, and got myself indoctrinated in a few keyboard shortcuts. So I'm learning stuff.
Anyway, the macro moves the cursor inside Group A first and sorts it, then to B and sort, then to C and sort. My criteria were Pts, +/-, GF. I had set the sort up prior to recording the macro.
Tested it out by putting in additional scores that would upset the standings. Like a charm.

This is not the result I really wanted. I still would like to have those standings automatically sort. After group matches, the top two from each group advance, and the 2 best third place teams advance as well.
So if this would automatically sort, I can write a cell telling it that once a certain game is played, to then go to the standings page and select the name of the appropriate team.
Once the group phase is over, and standings are final, the Schedule page will send appropriate team names for the Semi final, and the Final.
So if all of that could be done without me doing anything but entering scores, I'd be a happier camper.
Attached is the updated file.

And FDibbins, thank you for the suggestion. It's much easier than how I had been doing it before.
Bob