/docs/community?hl=en
This content is likely not relevant anymore. Try searching or browse recent questions.
Automatic League table sorting
1
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

https://docs.google.com/spreadsheets/d/1KqwCQAcow9RfS6gxdMQW7d4uyUMJ0SM-cYoR_J9G_-c/edit?usp=sharing
Details
Pinned
Locked
Latest Update Latest Updates (0)
Relevant Answer Relevant Answers (0)
All Replies (13)
Relevant Answer
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
Relevant 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
Relevant 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
Relevant 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
Relevant 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.

https://docs.google.com/spreadsheets/d/1YkNmpMaoZ3CHP-0rKjaV0q58hZjR_7D4Fk8huzNrRQ8/edit?usp=sharing
marked this as an answer
This question is locked and replying has been disabled.
Discard post? You will lose what you have written so far.
Write a reply
10 characters required
Failed to attach file, click here to try again.
Discard post?
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?

A problem occurred. Please try again.
Create Reply
Edit Reply
This will remove the reply from the Answers section.
Notifications are off
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.

Reported post for abuse
Unable to send report.
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.

Reported post for abuse
Unable to send report.
This reply is no longer available.
/docs/threads
//accounts.google.com/ServiceLogin
You'll receive email notifications for new posts at
Unable to delete question.
Unable to update vote.
Unable to update subscription.
You have been unsubscribed
Deleted
Unable to delete reply.
Removed from Answers
Removed from Updates
Marked as Recommended Answer
Marked as Update
Removed recommendation
Undo
Unable to update reply.
Unable to update vote.
Thank you. Your response was recorded.
Unable to undo vote.
Thank you. This reply will now display in the answers section.
Link copied
Locked
Unlocked
Unable to lock
Unable to unlock
Pinned
Unpinned
Unable to pin
Unable to unpin
Marked
Unmarked
Unable to mark
Reported as off topic
Known Issue
Fixed
Marked Fixed
Unmarked Fixed
Unable to mark fixed
Unable to unmark fixed
/profile/0
false
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
35
false