This content is likely not relevant anymore. Try searching or browse recent questions.
Automatic League table sorting
Hello everyone

I am trying to set the league table to automatically adjust the position based on the points (blue column points) from the Weeks sheet to Position Table sheet.

Could you please set the formula to sort the entire rows automatically depending of the position each time (every week) we update the Weeks sheet?

We apreciate the help, we are very lost at this.

Thanks for your help

Details
Pinned
Locked
Latest Update Latest Updates (0)
All Replies (13)
Hello Joel,

I would like to ask some questions regarding how you use the sheet.

1. As time goes by, will new tables be added to sheet Weeks?
2. Are players and scores in each week related? Do we still need data from the past weeks for the Position sheet?
3. Are the weekly tables updated manually each week? Or the tables come from some source automatically?
4. I ask the 3rd question to check if it is possible to modify the structure of Week sheet. If we change some structure the work would be easier.

Cheers!
marked this as an answer
Hello IceSwan,

First of all, thank you for responding.

1. Yes, one will be added each week and some tables will be 8 players and other times they will be 12 players (The first two weeks are simply a template)

2. Yes, they are related. Different players can participate each week, but we would like the Position sheet to collect the data from all the weeks and create an automatic classification depending on the points earned (blue column points).

3/4. The games results are updated manually, yes (and the rest of the columns are using formulas to complete themselves, but i think you know that already). So feel free to change something if it simplifies your work.

Thanks again.
marked this as an answer
Hi again,

I try to preserve the structure of your sheet.
I have try my solution in sheet Ice.

Before we go further, I'm not sure what is the "GP" (Column C of the position table) so I put random thing there for now.

I think may be my method was not so elegant but it should work, hopefully.

Here is what I have done:

First, cell B2 formula is
```
`=MAX(FILTER(ROW(Weeks!A1:A);LEFT(Weeks!A1:A;4)="Week"))`
```
This part is to find the "header row of the last table". Basically I try to find the last row that start with "Week". Because I will use this number later, I decide to keep it in a cell. Should be easier to debug.

Second, cell D2 formula is
```
`=MATCH("W";INDIRECT("Weeks!A"&B2&":DZ"&B2);0)`
```
This is to find the column where the "W" is. Because the rest of the data in this table will follow "W", I decide to find it and store the column order in D2.

Lastly, at the top left of result table (B4) is my lengthy query
```
`=QUERY({INDIRECT("Weeks!A"&B2+1&":AZ")};"select Col1, Col2, Col"&D2&", Col"&D2+1&", Col"&D2+2&", Col"&D2+3&", Col"&D2+4&", Col"&D2+5&", Col"&D2+6&", Col"&D2+7&", Col"&D2+8&", Col"&D2+9&", Col"&D2+10&", Col"&D2+11&", Col"&D2+12&" where Col1<>'' order by Col"&D2+14;0)`
```
I use B2 to capture the first row of data
I use D2 to mark the first column with data
I select these columns with Col#, # are calculated from D2 D2+1 D2+2 .....
I remove blank rows with  "where Col1<>'' "
I sort the data with " order by ", Col D2+14 should be your position. I could order by the score column too. But I decide to use your position because I'm not sure about rules of the game.

// I'm not really good at QUERY. Maybe other people here will find a far more clever way. lol
marked this as an answer
Hi again IceSwan,

Forget the acronym GP, I have removed it from the table.

As for the operation of the automatic table, it is just what we wanted, it works perfectly you are GOD. But the thing is, we want all the points of every week to be added (blue column points) and the formula is only counting the last week.

So I have made a change regarding the organization of weeks so that the column of points is always in the same position (blue column points).

I have separated the weeks into 8 players (Weeks8 sheet) and 12 players (Weeks 12 sheet) because the tables have different sizes.

Then, could all the points of the Week8 and Week12 sheets be added, plus the ones that are added in the following weeks in the Position Table?

Thanks again for the immense help, like I said, we are so lost at this.
marked this as an answer
Of cause, click on the link and request access, please add some message to let me know this is you. This way I will get your email.

marked this as an answer
This question is locked and replying has been disabled.
10 characters required
Failed to attach file, click here to try again.
You will lose what you have written so far.
Personal information found

We found the following personal information in your message:

This information will be visible to anyone who visits or subscribes to notifications for this post. Are you sure you want to continue?

This will remove the reply from the Answers section.
Your notifications are currently off and you won't receive subscription updates. To turn them on, go to Notifications preferences on your Profile page.
Report abuse
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Report post
What type of post are you reporting?
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

This reply is no longer available.
Search
Clear search
Close search