Filter issue with differently sized cells in Excel
drakar3
Posted messages
16
Status
Membre
-
Theo.R Posted messages 585 Status Membre -
Theo.R Posted messages 585 Status Membre -
Hello everyone,
I've seen this question asked before and it seems to me that there is no solution, which is surprising for software like Excel.
I have created a ranking by team of 2 to 4 runners:
- each runner scores points for the team and the idea is to filter based on the team's total points.
So I want the filter to move all the cells of the team, which are obviously of different sizes. And of course, Excel tells me it's not possible!
So, I'm forced to enter a formula on the right to sort in ranking order and then cut and paste each team one by one into a new table... Is there really nothing better to do in 2016 with Excel????
Thanks in advance,
I've seen this question asked before and it seems to me that there is no solution, which is surprising for software like Excel.
I have created a ranking by team of 2 to 4 runners:
- each runner scores points for the team and the idea is to filter based on the team's total points.
So I want the filter to move all the cells of the team, which are obviously of different sizes. And of course, Excel tells me it's not possible!
So, I'm forced to enter a formula on the right to sort in ranking order and then cut and paste each team one by one into a new table... Is there really nothing better to do in 2016 with Excel????
Thanks in advance,
6 réponses
Hello
what do you call cells of different sizes? If they are merged cells, the filter won't work!
But in this case, what compels you to merge them "of course"?
Looking forward to hearing from you
--
The quality of the answer mainly depends on the clarity of the question, thank you!
what do you call cells of different sizes? If they are merged cells, the filter won't work!
But in this case, what compels you to merge them "of course"?
Looking forward to hearing from you
--
The quality of the answer mainly depends on the clarity of the question, thank you!
On the attached image, there is a logo, then on the right the overall score (under points) and the names of the 4 runners followed by their respective scores... it should be possible to sort by the overall score only...
Hello,
I think it's possible, but not through a "standard" solution pre-designed by Excel. I have a macro in mind that could be launched via a button to regenerate the ranking based on each team's points.
It's not a very complicated code in itself, but given the specifics of your request (cell sizes, etc.), I would prefer to have an Excel document from you and a precise request regarding what you expect before I dive into VBA :-)
In short:
- Please upload a basic document via www.cjoint.com
- Please list your SPECIFIC expectations (wishes, constraints, etc.). Especially think about how you will fill out your document. This will define how we should design the solution ;)
Best regards,
I think it's possible, but not through a "standard" solution pre-designed by Excel. I have a macro in mind that could be launched via a button to regenerate the ranking based on each team's points.
It's not a very complicated code in itself, but given the specifics of your request (cell sizes, etc.), I would prefer to have an Excel document from you and a precise request regarding what you expect before I dive into VBA :-)
In short:
- Please upload a basic document via www.cjoint.com
- Please list your SPECIFIC expectations (wishes, constraints, etc.). Especially think about how you will fill out your document. This will define how we should design the solution ;)
Best regards,
That's very kind of you.
I am attaching the document.
The principle is simple: after each event, points are awarded to runners.
These points feed into the total box.
The objective is then for the ranking to update, from the largest to the smallest number of points.
I am attaching the file. :)
Thank you very much in advance,
I am attaching the document.
The principle is simple: after each event, points are awarded to runners.
These points feed into the total box.
The objective is then for the ranking to update, from the largest to the smallest number of points.
I am attaching the file. :)
Thank you very much in advance,
Here is the link:
https://www.cjoint.com/c/FAspqTrS1ni
As you will see, not all teams have been created, and the total points are therefore in the 30 boxes located under "Pts".
https://www.cjoint.com/c/FAspqTrS1ni
As you will see, not all teams have been created, and the total points are therefore in the 30 boxes located under "Pts".
Hello Theo,
Actually, it doesn't quite fit:
- the idea is for the 30 teams to be ranked in descending order: 1st to 10th on the 1st page (league 1), 11th to 20th on the 2nd page (league 2), and 21st to 30th on the 3rd page (league 3).
- knowing that I have improved a few aspects of the documents.
- and that it is possible I will add one or two leagues in the future (so league 4 and league 5).
Does it seem possible for you to adapt your macro?
Thanks again :)
The document: https://www.cjoint.com/c/FAuqjgSh3fi
Actually, it doesn't quite fit:
- the idea is for the 30 teams to be ranked in descending order: 1st to 10th on the 1st page (league 1), 11th to 20th on the 2nd page (league 2), and 21st to 30th on the 3rd page (league 3).
- knowing that I have improved a few aspects of the documents.
- and that it is possible I will add one or two leagues in the future (so league 4 and league 5).
Does it seem possible for you to adapt your macro?
Thanks again :)
The document: https://www.cjoint.com/c/FAuqjgSh3fi
Hello,
No worries about the response time, the only thing is that I currently don't have the time with my job to revisit my code and adapt it to your specifications.
But there's a good chance that I will have to start more or less from scratch, my code was specific to a situation with a precise operating procedure.
Anyway, I'm sorry but I won't be able to process your request for the moment, I hope that other macro enthusiasts will see this message and can take over ;)
Best of luck,
P.S.: If the topic falls into the depths of oblivion, feel free to close it and open a new one with a concise and complete description of your request ;)
No worries about the response time, the only thing is that I currently don't have the time with my job to revisit my code and adapt it to your specifications.
But there's a good chance that I will have to start more or less from scratch, my code was specific to a situation with a precise operating procedure.
Anyway, I'm sorry but I won't be able to process your request for the moment, I hope that other macro enthusiasts will see this message and can take over ;)
Best of luck,
P.S.: If the topic falls into the depths of oblivion, feel free to close it and open a new one with a concise and complete description of your request ;)
In the absence of further details on the request, below is the code that I was able to create.
Preliminary operation: - the sheet with the results must be named "RANKING"
- There must only be 10 teams (cells B6:B12 to B60:B64 in the example)
- In case of a tie score, the team that appears first in the list takes the higher place (may be unfair but oh well!)
Best of luck:
Sub Ranking()
Application.ScreenUpdating = False
For i = 6 To 60 Step 6
Range("B" & i).Value = WorksheetFunction.Rank(Range("E" & i).Value, Range("E:E"))
Next i
For j = 60 To 6 Step -6
If WorksheetFunction.CountIf(Range("B:B"), "=" & Range("B" & j).Value) = 1 Then
Else
Range("B" & j).Value = Range("B" & j).Value + 1
End If
Next j
Set Original = Sheets(2)
Original.Copy Before:=Sheets(2)
Set Copy = Sheets(2)
Original.Select
ActiveSheet.Pictures.Delete
For k = 6 To 60 Step 6
If Copy.Range("B" & k).Value = 1 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B6:B10").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 2 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B12:V16").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 3 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B18:V22").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 4 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B24:V28").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 5 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B30:V34").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 6 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B36:V40").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 7 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B42:V46").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 8 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B48:V52").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 9 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B54:V58").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 10 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B60:V64").Select
ActiveSheet.Paste
End If
Next k
Application.CutCopyMode = False
Application.DisplayAlerts = False
Original.Range("B2").Select
Copy.Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Preliminary operation: - the sheet with the results must be named "RANKING"
- There must only be 10 teams (cells B6:B12 to B60:B64 in the example)
- In case of a tie score, the team that appears first in the list takes the higher place (may be unfair but oh well!)
Best of luck:
Sub Ranking()
Application.ScreenUpdating = False
For i = 6 To 60 Step 6
Range("B" & i).Value = WorksheetFunction.Rank(Range("E" & i).Value, Range("E:E"))
Next i
For j = 60 To 6 Step -6
If WorksheetFunction.CountIf(Range("B:B"), "=" & Range("B" & j).Value) = 1 Then
Else
Range("B" & j).Value = Range("B" & j).Value + 1
End If
Next j
Set Original = Sheets(2)
Original.Copy Before:=Sheets(2)
Set Copy = Sheets(2)
Original.Select
ActiveSheet.Pictures.Delete
For k = 6 To 60 Step 6
If Copy.Range("B" & k).Value = 1 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B6:B10").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 2 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B12:V16").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 3 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B18:V22").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 4 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B24:V28").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 5 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B30:V34").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 6 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B36:V40").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 7 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B42:V46").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 8 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B48:V52").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 9 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B54:V58").Select
ActiveSheet.Paste
End If
If Copy.Range("B" & k).Value = 10 Then
Copy.Select
Range("B" & k & ":V" & k + 4).Select
Selection.Copy
Sheets("RANKING").Select
Range("B60:V64").Select
ActiveSheet.Paste
End If
Next k
Application.CutCopyMode = False
Application.DisplayAlerts = False
Original.Range("B2").Select
Copy.Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub