Petanque competition file Solved

Question

Good evening,

I am modifying a file to adapt it to my way. I have a problem with the "summary" tab where I cannot find the formulas to calculate the points for the sections (I want to delete the P1 to P5 tabs and replace them with Part 1, etc.). I think I will have other issues later, but for now, can you help me as usual?
I have attached the file.
Thank you in advance for your feedback.
Best regards
dodo

https://mon-partage.fr/f/18QZexBs/

Configuration: Windows / Chrome 84.0.4147.135

Raymond PENTIER · Answer

Good evening.

I didn't quite understand what you want to do:

The sheets P1 to P5 are empty, the sheets Part 1 to Part 5 are filled in.
In the Recap sheet, the formula in L3 refers to sheet P1 but also to the range Manche1.
If you replace P1 with Part 1, you will need to redefine the ranges Manche1 to Manche5.

Is this what you want?

--
It’s nice, retirement! Especially in the Antilles...
Raymond (INSA, AFPA)

DodoMichelle · Answer

Hello,  That's exactly what I would like! I can't see where to find manche1, etc. I'm also not sure if the file works correctly due to the changes I've made. Thanks for your help. Best regards. Dodo

via55 · Answer

Hello Dodo

Your file with the necessary formulas in Recap
https://mon-partage.fr/f/qlnHHK7R/

However, I don't understand your Ranking sheet

Best regards
Via

--
"Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,
I hope you are doing well and that you enjoyed a bit of your vacation!
Thank you for your response.
I have continued the ranking sheet, but I had added a simple ranking on the right side by PG and Points made, but it's acting up as well as the recap tab.
Please take a look at the file you created.
Best regards.
dodo

via55 · Answer

How do you want to rank it first by the number of matches won, then by the points scored of course and finally in case of a tie in points?

DodoMichelle · Answer

Good evening Via, The ranking: games won, then points scored, then points allowed. Have a good evening. Best regards Dodo. ????

via55 · Answer

Re

See if that works for you
https://mon-partage.fr/f/Rmv9F0a8/

No need to run a macro for the ranking, everything is done with formulas

Best regards
Via

--
"Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hi again,

In principle, OK, but I still have occasional bugs on the "recap" tab when I run the "Tirage" macro and I only have 54 teams listed in the "classement" tab. Is it one of the macros that causes the issues?
Best regards,
Dodo

via55 · Answer

Possible I haven't looked at the macros
As soon as you have a bug send me the file with the bug so I can see what it's about
I'll look into it tomorrow

Best regards
Via

--
"Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,
Below is a file where I clicked once on the "Draw" button in the "List" tab.
Bug in the "Recap" tab
Bug in the "Ranking" tab and no more ranking from line 56 onwards.
I think it’s my macros that are causing these issues?????
Best regards
Dodo

https://mon-partage.fr/f/OIaM8HxA/

via55 · Answer

Hello Dodo

In the sent file, cell N60 of Lists is empty which is causing the error
I tried to do several redraws without being able to trigger a new error
Looking at the macros, I didn't notice anything unusual or that could cause this

As it stands, I think you should check after each draw if everything is correct and if not, do another redraw

Best regards
Via

--
"Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  It seems there is just one issue left in the "ranking" tab (see file below). I don't see why! Best regards Dodo.    https://mon-partage.fr/f/oy4VKOb2/

via55 · Answer

Re

The ranges in the formulas in columns G and H do not go low enough
It is necessary
=RANK(F3,$F$3:$F$194;0)
and =RANK(G3,$G$3:$G$194,1)+COUNTIF(G$3:G3,G3)-1

Best regards
Via

--
"Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,

I did as you suggested, apparently it works, but I have issues with the "recap" tab line AA!
When I do a "reset" as well.
Normally, at the beginning I put 1 in the list column H, then the registration button and the draw button.
If you could run some tests with the attached file to see what’s happening?
Thank you
Best regards
Dodo

https://mon-partage.fr/f/fBUzl6R1/

via55 · Answer

Hello Dodo

The error occurs every time there is an empty cell in column N of the List after a draw
Why does a mystery empty cell sometimes remain
Your draw procedure is a bit of a mess!

As a remedy, try putting error handling in some of the Recap formulas
In D3:
=IFERROR(IF(MID(R3,2,1)<"D",INDIRECT("'Partie 1'!D"&RIGHT(R3,LEN(R3)-3)),IF(MID(R3,2,1)<"J",INDIRECT("'Partie 1'!F"&RIGHT(R3,LEN(R3)-3)),IF(MID(R3,2,1)<"O",INDIRECT("'Partie 1'!N"&RIGHT(R3,LEN(R3)-3)),INDIRECT("'Partie 1'!P"&RIGHT(R3,LEN(R3)-3))))),0)
Do the same in F3, H3, J3, and L3, then drag down the columns

Best regards
Via

--
"Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,

I did what you suggested. There are still a few issues. I attached a modified file.
1) delete the previous list
2) Test 1 or Test 2 to have a complete or partial list of players
3) Enter the players list
4) do the draw (several times to avoid the empty cell bug in column N).
There is still the "recap" tab that has a bug; the results are incorrect.
Some results are correct while others are not, to be checked.
If you can still spare me some time!
Best regards
Dodo

https://mon-partage.fr/f/mP9n6onj/

via55 · Answer

Hello Dodo

I believe you have built a complicated setup with the draws; I'm not really sure how they work.
In case of a partial list, it doesn't function.
I think the entire draw procedure needs to be reviewed.
What is the principle? For each round, the composition of the triplets changes, and players who have already played together should not end up together again.

--
"Imagination is more important than knowledge." A. Einstein

via55 · Answer

Re,

Without your response, I have resumed the drawing and the distribution in the games
Once the 1s are set for those present, the Draw button generates random numbers in column K, and the formulas in the following columns distribute the players

The Clear previous list button erases the 1s and the random numbers
I have taken the 5 game sheets and added Clear buttons to empty the previous scores

I have deleted all empty modules in the editor
https://mon-partage.fr/f/KiQ6GWzW/

--
"Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  First of all, a big thank you, but if I haven't replied, it's because I'm trying to sort out my file, as it really is a mess and I need to be much clearer in my requests! I will look at your work and in any case, I remain connected to move forward on the subject! Best regards dodo

DodoMichelle · Answer

Hello Via,  I ran several tests on your file, which is indeed much simpler than the database! I think the "enter the list of players" button and "macro 7" are no longer useful, as well as columns A and B. I will modify it if you confirm. However, I'm experiencing a bug with 192 teams on the summary and ranking of team AA. Thanks again for the work done. Best regards Dodo .

via55 · Answer

Hello Dodo  Yes, you can delete them, but please erase columns A and B without actually deleting them because that would mess up the formulas as for the bug, it wasn't too serious, the formula in M7 of Lists is missing, stretch the one from M8 above and everything should fall into place  For safety, you can then hide columns J and onwards and add a final line to the Tiragesparties macro assigned to the Draw button: Msgbox "Draw executed"  Keep testing and keep me updated  Regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Re Via,  I have made the requested changes. I still have a bug in the "recap" tab, maybe it's my fault! I replaced Part 1 with the actual part number in the formulas because all the cells contained Part 1 instead of the corresponding parts. See the attached file.  https://mon-partage.fr/f/sbYy8J67/  Best regards Dodo

via55 · Answer

You did well, it must have been an oversight on my part. However, I don't see any bugs in the file sent! I made several reprints without encountering any bugs.  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Re Via,  Once again, I’m not clear in my words! The problem is that several players have won 8 "ald" games with a maximum of 5! Best regards Dodo

via55 · Answer

Re  Yes, there was an error in the formulas of columns F and following (same formulas as in D and E) Rectified and improved the ranking table to show the points conceded and MFC to differentiate the lines (based on a hidden column) https://mon-partage.fr/f/qTZv2hiB/  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  Impressive work! However, I just saw through my simulations that the players in columns A, G, K, and Q (tabs Games) play against each other in every match. Only the 2nd and 3rd players change in the composition of the triples. Can we also have a random drawing for the 1st player of each triplet? I hope I'm a bit clearer than usual. Best regards, Dodo https://mon-partage.fr/f/cSlogryw/

via55 · Answer

Hello Dodo  https://www.cjoint.com/c/JIqorQbFhYw To test  -- "Imagination is more important than knowledge."    A. Einstein

DodoMichelle · Answer

Good evening Via,  Apparently, there are no issues with the random parts, but I just realized that in the "summary" tab, the "P" column, which represents conceded points, only takes into account the points when the team has won. For example, AA conceded 12 points in the 2 winning games but 3 times 13 in the 3 losing games, so in total 51 points! The same goes for each team. In summary, the P column overlooks the conceded points in the lost games. Best regards Dodo

via55 · Answer

Good evening Dodo  In E3, remove IF(D3<13;0; at the beginning of the formula, same in G3 etc. and extend the formulas  Best regards Via -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  I made the changes and also modified the formula in column "P" to get the correct number of games won, but the "summary" and "ranking" tabs are disturbed with a number of players

via55 · Answer

Hello Dodo  Oh yes, then you need to enclose the formulas with a IFERROR(... ;0) as in column D  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via, I have completed your recommendations and after several tests, everything seems fine! And it's not "a convoluted mess". I would like to create a conditional formatting on the "list" tab with cell E33 but I have a problem, as well as putting a password on the macros! If you can still help me? Thanks again for the work done..... Best regards Dodo  https://mon-partage.fr/f/5NoNqFgx/

via55 · Answer

Hello Dodo  I think the MFC is used to color the name cells in the table; it’s done with the formula =I1=$E$33  To protect the formulas, I first protected all the sheets with the password dodo, and therefore added an ActiveSheet.Unprotect "dodo" and an ActiveSheet.Protect "dodo" in all the macros which will of course need to be modified if you change the password (this can be done all at once with Replace)  The principle to protect the macros is simple: - first, they all need to be set to Private Sub so they do not appear in the Ribbon view - then we protect the VBA with a password: in the editor Tools - VBA Project Properties - Protection, I used the same password  https://mon-partage.fr/f/Dmhwpf1L/  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  For the MFC: it's column I and not cell I7, right? Macros: I don't see how to put them in Private Sub? As for the protection and the password, it's all good, I saved it  (you explained it to me last time). I did multiple tests, so far so good, except for 6 players (maybe that's normal?). The table is really user-friendly! Have a great weekend Best regards Dodo  https://mon-partage.fr/f/EECIrhne/

via55 · Answer

Hello dodo

For the MFC, if you look in the MFC, you will see that it applies to columns $I:$AI, so in the formula we use the first cell of the table (I1)
I set the macros to Private, check in the editor (you just need to add Private in front of Sub)
Yes, for 6 players it's normal, not enough players to rotate in each game, but will you ever have tournaments with only 6 players?

Have a good weekend as well
Regards
Via

--
"Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Re Via,  Thank you very much for everything, I've taken note of your explanations! I think I have a file that's TOP...... I already have another issue to bring to you, but I'll let you focus on other tasks for now. Good luck and see you soon. Best regards dodo

via55 · Answer

Re  I'm eagerly awaiting your problem; for now, I'm recovering stuck at home, so I have more time on my hands! Best regards Via -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  I have noted your impatience (I hope above all that your issue is not serious!). I actually have 3 requests:  1) I would like to have the same file in doubles as triplets (96 players in each column)  2) With the previously created triplet file, I would like to make a random draw according to the attached file, that is to say: the first 64 players in columns Q U Y AC AG, the next 64 in columns R V Z AD AH and the last 64 in columns S WW AA AE AI.  3) The same file in doubles as 2), with 96 in the 1st column and 96 in the 2nd.  Best regards Dodo https://mon-partage.fr/f/aeqgwvjB/

via55 · Answer

Good evening Dodo  Does that mean all members will be present on the 2nd and 3rd?  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  Actually, the principle is the same as the first file you made for me. I will say from 0 to 192 players. If, however, there are more players in one color than in the others, I will balance the three compartments in column I at registration before starting the draw. Good luck. Best regards Dodo

DodoMichelle · Answer

Re, I forgot to mention: Green cells: Pure shooters Blue cells: Midfielders (shooting and pointing) Yellow cells: Pure pointers Best regards. Dodo

via55 · Answer

Re  The 3 files. To be tested as usual https://mon-partage.fr/f/R89Zjf1A/ https://mon-partage.fr/f/SlMOABOH/ https://mon-partage.fr/f/yxgYQGiR/  I noticed that in the Goalaverage table of the Ranking, the absent players were interspersed among the others instead of being at the end, so I modified the formula in column Q to assign the absentees a bogus goal average of -1000 to classify them last during sorting  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  I thought I would keep you busy for a while, but actually you're going to be bored this week!!!! More seriously, for now I've just done some tests, which seem good so far. I've just noticed in the last file  (duplicates with sections) a few N/A appearing when entering 1 for registrations. OK with the corrections you made. I am very happy with the result, and it was very quick too! Best regards Dodo

via55 · Answer

Hello Dodo  These N/A's are not normal, where do they appear and in what case?  -- "Imagination is more important than knowledge."    A. Einstein

DodoMichelle · Answer

Good evening Via,  Attached below is an example where column H is in red even though it is an even number, so it's suitable for pairs and N/A. Best regards Dodo https://mon-partage.fr/f/0XjNbjxd/

via55 · Answer

Hello Dodo,  1) Red because divisible by 4 yes but choice only in the first half of the table, the MFC I set, look at the formula =OR(MOD($H$201;4)>0;COUNTIF($H$7:$H$102;1)COUNTIF($H$103:$H$199;1)), requires that the number of players taken from the first half of the table be equal to that taken from the second half I put divisible by 4 because in my idea there needed to be at least 4 players and therefore that all choices would be multiples of 4 but thinking about it, it's wrong because with 10 players we will have 5 doubles and that's fine. Then correct in the MFC formula by replacing 4 with 2  2) N/A because I forgot to enclose the formula with an IFERROR in column R, I must have been under the effects of anesthesia!!  3) But as soon as you perform the draw, you have all the names that are entered in the table if you have checked equal number in the upper and lower parts of the list (otherwise it does not work you have blanks) if I understood correctly what you wanted It’s in the other doubles file that you can choose names anywhere in the list  Best regards  Via    -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  1) I've corrected the MFC: it's good and it's fine that it's red as long as there isn't the same number of players in the 2 "series". 2) Only the AA line in the R column didn't have the right formula, I've fixed it, OK.  I'll continue testing, but I believe perfection is close or achieved!!!!!!  However, I will have other similar topics to propose to you for a simple solution like you do, if you agree and if you still have time. Thanks in the meantime Best regards Dodo

via55 · Answer

Hello Dodo  Good ☺ I’m waiting for your other topics Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  I haven't had the time to do further tests, but I already have another topic to bring to you. I have a file for a competition in 4 parts with 32 teams (winners against winners) developed with your help. Sometimes, this type of competition has a very variable number of teams. It can range from 10 teams to a hundred teams (rarely). Is it possible to construct this type of table (attached) regardless of the number of teams? https://mon-partage.fr/f/LFkEjll1/ Note: Am I following the procedure by submitting my issues this way or not? Best regards Dodo

via55 · Answer

Good evening Dodo  As the file is filled with formulas, it might be complicated, it would be better to do it via a macro, I will look into it  Why would you be out of procedure?  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  I tend to think that when a solution is found for the presented issue, the requester should click on thank you to close the request and should ask their other question, possibly on the CCM site?  Best regards Dodo

via55 · Answer

Hello Dodo  Yes, when the topic is closed, it's better to open another thread, but it's not closed right now To close it, you need to click on the icon with the ! in the top right corner and mark the question as resolved Then the title will appear in green in the lists  For now, I'm struggling with your file, everything seems to be working out okay, but in the trials, I have an odd number of players who have won all 4 games, which doesn’t seem normal!  Best regards Via   -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  OK for closing the issues. It seems normal to me that depending on the number of teams involved, it sometimes happens to have an odd number of winners after 4 rounds! For example: 34 teams, in the 1st round 17 winners, in the 2nd round 8 or 9 winners, in the 3rd round 4 or 5, and in the 4th round 2 or 3. Best regards Dodo

via55 · Answer

Hello Dodo  Indeed, an odd number can happen, I was overthinking it for nothing!  Solution without a macro finally using an auxiliary table (Results sheet) https://mon-partage.fr/f/6hOw3jYb/ all sheets are protected (without a password for now; if you want to set one, you'll need to include it in quotes after the Unprotect and Protect in the RAZ and sort macros)  Procedure:  Registration Sheet: RAZ to clear old data RANDOM SORT that also edits the alphabetical list on the right CONTEST that activates the Contest sheet by clearing previous scores  Contest Sheet Enter the scores after each round, the pairs for the next round will then be displayed Highlight a particular day as before if needed  Results Sheet completely protected, nothing to do except view a player if needed, just like in Contest  To be tested as usual  Best regards  Via

DodoMichelle · Answer

Good evening Via,  Honestly, your work is truly impressive!!!!!!!!! I can’t find the words to describe it. I’ve done some tests with up to 100 players, and I haven’t encountered any issues. There is just one thing in the file below, after a reset, team 1 is displayed in the competition and results tab without any apparent consequences. I will continue to run tests. Thank you, thank you..  Best regards, Dodo  . https://mon-partage.fr/f/TuQkJvpo/

via55 · Answer

Re,  Problem solved with a conditional formatting that turns text white as long as the first team is not registered. Another issue resolved is the previous alphabetical list that did not clear during the RAZ. https://mon-partage.fr/f/I0pxy85W/  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  So far, no bugs after several tests. What efficiency!  There is still a display on line 4 of the contests tab that doesn't hinder anything, but...  I have another request regarding the file below that you created for me recently:  I am going to "organize" a competition with 4 or 5 clubs for which I would need to create, in addition to the individual ranking, a club ranking based on the top 4 players from each club (by adding their positions: e.g., 1st + 8th + 11th + 19th equals "39 points" and the one with the least points will be 1st).  I think you need to include the player's name and the club's name in the adjacent column when registering and then find the rank?  Once again, if you can help me???  Best regards  Dodo   https://mon-partage.fr/f/dWUvlzMy/

via55 · Answer

Good evening Dodo  You are absolutely right Changes made accordingly https://mon-partage.fr/f/rIL93n24/  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  Great job on your modification! I tried to replicate what you did for the "Goal average" ranking, which is the second way to rank, and I'm encountering a bug with the "Ranking" macro that I wanted to modify to protect it! I don't know what to do. Best regards Dodo   https://mon-partage.fr/f/lIuXo9Of/

via55 · Answer

Hello Dodo  The bug comes from the fact that you're unprotecting the sheet before selecting it, whereas it should be unprotected afterwards  Best regards  Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Re, Thank you, I thought it needed to be unprotected at the start, I wouldn't have found the bug! One last thing for this file that will "fringe" on excellence: Is it possible for the conditional formatting of column H to make that same column white only when there is the same number of players in each series, e.g., "4" or "8" or "X" in blue, green and yellow. Best regards Dodo  https://mon-partage.fr/f/fVbBmSv2/

DodoMichelle · Answer

Re,  Once set to Private in a macro to make it invisible, what do I need to do to make it reappear? Best regards Dodo

via55 · Answer

Re  1) Replace the MFC formula with this one: =OR(MOD($H$201,6)>0,OR(COUNTIF($H$7:$H$70,1)COUNTIF($H$71:$H$134,1),COUNTIF($H$7:$H$70,1)COUNTIF($H$135:$H$198,1),COUNTIF($H$71:$H$134,1)COUNTIF($H$135:$H$198,1)))   2) Just remove the Private in front of Sub  Regards Via -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  Everything is fine and it's just as I thought, the file is very functional and user-friendly, well done!  On this other file, attached below, I just have line A4 in the members competition tab that remains at 1 during a reset. I haven't found a solution, unfortunately, as is often the case! Best regards Dodo  https://mon-partage.fr/f/oApBwu91/

via55 · Answer

Hello Dodo  https://mon-partage.fr/f/0SNwRNZI/ The solution is of course a conditional formatting, but it must be based on a cell from another sheet, the Inscriptions sheet, because it happens when no team has been entered, so I pointed to an empty cell, namely R5 But to refer to a cell from another sheet in conditional formatting, it needs to be named, so I named R5 equipe1 and the conditional formatting formula is =equipe1=""  Best regards Via   -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  I haven't reached out to you in a while! Having finalized the duplicates file with 2 series, I just noticed that each team only faced 2 different teams over the 5 matches! Are the changes I made affecting the distribution? I don't think so, but? F https://mon-partage.fr/f/6S70YlyN/ file below Do we need to modify the macro?? Best regards Dodo

DodoMichelle · Answer

Re, I am resending the file, I think I "glitched"   https://mon-partage.fr/f/EQmXjtLJ/ Best regards dodo

via55 · Answer

Hello Dodo  No, these are not the modifications; the formulas shifted the names a few notches, but of course, we will encounter the same opponent one game out of two! We need to mix the names, but that's not with a formula but through a macro; I'll look into it as soon as I have a bit of time.  We agree, in each match, the doubles partners change and should not face opponents they have already played against, right?  Best regards Via   -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  Yes, in this file there are 96 shooters (blue color) and 96 pointers (black color), 1 blue player plays with 1 black player and changes teammates for each match and, as much as possible, they should not meet twice in the competition. Formations can be made randomly between blues and blacks, but never 2 blues or 2 blacks together. Thank you and good luck. Best regards, Dodo

DodoMichelle · Answer

Hi,  Actually, the problem is the same with all the files. I'm afraid there will be a lot of work??? Best regards Dodo   https://mon-partage.fr/f/oJaJYsdn/  https://mon-partage.fr/f/baKgxUfn/  https://mon-partage.fr/f/jhwfJQzi/  https://mon-partage.fr/f/TfbqcdBa/  https://mon-partage.fr/f/tVHiPcVE/

via55 · Answer

Re  By handling everything through macros instead of formulas, it’s a trial To test and see if it suits you in all scenarios If it works, we can apply it in the other files  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  There is no attached file! Sincerely dodo

via55 · Answer

Hi,  Sorry, I'm juggling too many things right now! https://mon-partage.fr/f/hQA6hlv5/  Best regards Via  -- "Imagination is more important than knowledge."    A. Einstein

DodoMichelle · Answer

Hello Via, No worries at all, I can see that you're very busy. That said, I just did quite a few checks and everything is "great" so far. I will continue testing and confirm with you by tomorrow before making changes to the other files! In the meantime, thank you, I'm honestly impressed by your responsiveness and analytical skills....... Best regards Dodo

DodoMichelle · Answer

Good evening Via,  I just performed several tests: In the first file, there is a bug on line 17 of the "summary" tab. In the second file, there are "missing" players in several matches.   https://mon-partage.fr/f/T5nZdvVK/  https://mon-partage.fr/f/Lk4ZFy87/  Best regards Dodo

via55 · Answer

Hello Dodo  1st file erroneous formula (I don’t know why?) line 17 col S to V, extend the formulas from the line above and everything will be back in order  2nd file: ouch! my macros work well when the number of duplicates is even but not when it is odd it seems I’ll take care of this as soon as I can, I’m overwhelmed right now!  Best regards Via

via55 · Answer

Re  I just looked, but how can it be that the number of doubles could be odd? In this case, there is one that does not play in each game and is therefore disadvantaged in the ranking.  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  I apologize for my two complaints, as much as you may be overwhelmed, I am feeling lost! In trying to do a multitude of simulations, I got my "pencils" mixed up. Indeed, there cannot be an odd number of doubles, that's obvious, and furthermore, I should have also seen that the formula in line 17 was incorrect!!!!! I will pay more attention from now on. Have a good Sunday Best regards Dodo

DodoMichelle · Answer

Good evening Via,  I just went through and organized my ongoing files, they are working. Can you, when you have a moment, modify the last 3 below like the P1v7IJOJ so that the players don't end up as partners or opponents multiple times in the same competition, I should leave you alone after that! Thank you. Best regards Dodo.   https://mon-partage.fr/f/P1v7lJ0J/   https://mon-partage.fr/f/PATKvyfw/  https://mon-partage.fr/f/tQsXW3s7/  https://mon-partage.fr/f/VjyJyf2A/

via55 · Answer

Hello Dodo  I don't have the password to unlock the VBA and the sheets in the triplet files  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  Another "blunder"! I removed the passwords. Just in case, it's the same as usual. Thank you. Best regards Dodo   https://mon-partage.fr/f/yLcj0HIi/  https://mon-partage.fr/f/QjZFnrqp/  https://mon-partage.fr/f/Ctvi4XFy/

DodoMichelle · Answer

Hello Via,  Did you receive my feedback (response 81)? I have a doubt, when I click on the last notification, I can't see it appearing! Best regards Dodo

via55 · Answer

Hello Dodo  Yes, I received your feedback, sorry for not responding immediately  However, I'm struggling with the macros for now After various attempts, even the one with duplicates isn't working correctly, some players can end up in consecutive matches I'm trying to find other solutions but nothing is working for now  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hi,  Thanks for your dedication. Best regards Dodo

DodoMichelle · Answer

Hi,  If it's easier with 4 parts instead of 5, I'm okay with that! I don't want to take up all your time or nearly all of it; I'm not the only one who needs your skills. Best regards, Dodo

via55 · Answer

Hi,  Not necessarily much simpler The problem is to find the right algorithm to avoid duplicates And I don't like to give up until I've found a solution ☺  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  I'm sorry to take up so much of your time with my issues, but in the file below, I've done a "simulation" with 18 players and 4 games (I can do without the 5th one if it simplifies the construction of the file). If you can "decode" the bases 6, 8, and 10 and think it's possible to build from them?????? Best regards Dodo  https://mon-partage.fr/f/4AKlF4vl/

via55 · Answer

Hello dodo  Don't be shy, I'm giving you the time I can, without encroaching on the rest, which is why you don't have a quick response at the moment. In your simulation, players end up in the same team in parts 1 and 4 and in part 3, there is playerA10 twice, but that must be an error; it should be A4 for the first one. https://mon-partage.fr/f/8Y0IPQyE/  And your simulation does not take into account the pointer, shooter categories..  For my part, I managed to create this table for 12 doubles; I still need to see how to model it and see if it works with more doubles. https://mon-partage.fr/f/gMGOs3sI/  Best regards Via   -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  I have taken a base in triplets (sheet 2 of the file), with annotations. I'm not sure if it's relevant? If it is, I could try to build a base 8 and a base 10! Happy decoding and good luck. Best regards Dodo   https://mon-partage.fr/f/uXq84RAw/

via55 · Answer

Hello Dodo  Your table is relevant and with a base 8 and a base 10 we will be able to cover the 30 possible cases from 6 to 64 triplets all players mixed  For the 5th part: if in base 8 and base 10 it's possible without redundancy that's perfect, otherwise from 36 players we can avoid redundancies by swapping for the 3rd line the teams from the different blocks, for example for 36 that makes 2 blocks in base 6 so we swap in the table for 36 A7 A8 A9 A16 A17 A18 from block 1 with A25 A26 A27 A34 A35 A36 from block 2  However, I still don't understand how you want to handle the shooters, because in your table all the players are mixed, but if we reserve the 3rd line of each part for the shooters by calling them B1, B2 etc as I did for the doublets we will inevitably have redundancies in more cases and the table has to be built differently  Best regards Via      -- "Imagination is more important than knowledge." A. Einstein

via55 · Answer

Re  Here is an attempt with a table for 18 doubles https://mon-partage.fr/f/TnxVA3tG/  If it’s good, once you have established bases 8 and 10, you will need to create the 29 other sheets for each possible number of triplets, naming them by the number of triplets; the formulas I put in the List will fetch the information from the correct place  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  Sorry, I had an immediate "bug" with table 18 doublets (duplicates and empty cells in part 5).  I created a base 8 (which I have checked multiple times) on 5 parts, I think it's good, even if the development is not rational (I would like you to validate or not), I'm really not sure of myself!  I'm still trying to create a base 10 while waiting for your feedback.  Reminder: the base 6 at the top of the file cannot be applied in 5 parts without redundancies.  Best regards Dodo   https://mon-partage.fr/f/nM3mF9i5/

DodoMichelle · Answer

Hello,  I just finished a base 10 in a more rational way than base 8. If you validate it, I will redo the 8. With these 2 bases, it is possible to make all the triplets (from 8 to 64 except for 22: a base 6 without redundancy is needed and I'm stuck for the moment). In bases 8 and 10, there are indeed the separate Shooters, which addresses my issue. I await your feedback. Best regards, Dodo  https://mon-partage.fr/f/dp9mg5KW/

via55 · Answer

Re  The white is due to a deleted formula, that's not a big deal but the rest I had to mix up the teams by transposing them from columns to rows I will take a look at that tomorrow evening but already looking a bit closer at your Simulator base 6 which I hadn't checked thinking everything was okay, I realize that players are facing each other multiple times, for example, A1 plays against A6 in matches 1 and 2, A2 plays against A17 in matches 2 and 4...  I will take a calm look at that tomorrow as well as your bases 8 and 10  If we can build these 3 bases correctly, we'll be good for everything I think  Best regards  Via

DodoMichelle · Answer

Hello Via,  I have reviewed base 6. Below is the file with 4 tabs: 1) Duplicates you created 2) Base 6 3) Base 8 4) Base 10 I hope the 3 bases are correct (I've done multiple checks, but.....) If you validate all three, I think, as you said, that we can cover all the triplets whatever the number from 6 up to.......... Could you please check all this? Have a nice day Best regards Dodo      https://mon-partage.fr/f/VITRSFod/

via55 · Answer

Hello Dodo  Verification done for bases 6 and 8 I have rearranged it to have the teams facing each other one below the other as in the files (I hope I haven't made a mistake) and I created (through macro) a table of opponents encountered below Unfortunately, many duplicates appear! https://mon-partage.fr/f/u91NnETS/  As a result, I haven't looked at base 10  I will try to compile the bases through macro but I'm not sure I'll manage  Best regards Via     -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  Indeed, the foundations are set for each shooter to play with different pointers in each game. I haven't checked, though, for each match, whether the shooters played against pointers they had already encountered!!! Moreover, I think that bases 6 and 10 are built without duplicates for the composition; I need to redo base 8. I will initially try to take care of the duplicates match by match (this evening?) before you bother creating something! Thank you Best regards Dodo

DodoMichelle · Answer

Re,  No matter how much I turn it around, I get stuck with the duplicates starting from the 3rd part, on base 6! While the composition might be good (with the exception of errors), I think it's impossible to avoid duplicates with 18 players! So for me, it's certainly possible to build with bases 8 and 10 (if I can confirm that), but the triplets with 14 or 22 teams, where base 6 is necessary, will definitely have duplicates, for sure. I'm not making any progress on my issue by saying this, but maybe, it takes up to 22 triplets to organize 4 winner vs. winner matches. Regards Dodo

via55 · Answer

Hey,  I looked into it a bit more and I think the minimum without duplicates will be a base of 12.  Considering only the pointers, to have a different partner we associate Part 1 Team 1 2 Part 2 Team 1 3 Part 3 Team 1 4 Part 4 Team 1 5 Part 5 Team 1 6  The opponents to avoid duplicates would be at least Part 1 Team 3 7 Part 2 Team 4 8 Part 3 Team 5 9 Part 4 Team 6 10 Part 5 Team 2 11  Best regards  Via   -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hi,  Sorry, I didn't understand everything! However, I took back the base 8 and if I haven't made any mistakes, there shouldn't be any duplicates (compositions and matches over 5 games). If you confirm that the base 8 is correct, this weekend I can create a base 10 and 12, and in that case, we will be able to cover 5 games from 16 Triplets up to 64 (noting that it will also be possible to do 8, 10, 12 but not 14). I will look at base 6 again on 4 games. I await your response before proceeding further. Thank you Best regards Dodo  https://mon-partage.fr/f/smHNqzHj/

via55 · Answer

Hi,  Sorry to disappoint you, but the pairs of pointers from part 5 are the same as in part 1.  Best regards, Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  What a blunder! I'm not fully awake yet, but is it better like this? Best regards Dodo   https://mon-partage.fr/f/BNdUB8xH/

via55 · Answer

Hello Dodo  It's better now, but there are still a few duplicates between shooters and pointers (which may be unavoidable) in the 5th section (P9 P10 P13 and P14)  Best regards Via

DodoMichelle · Answer

Re,  I have restarted the 5th part. I hope this time will be the right one, but after turning it every which way, I can no longer see the duplicates!!!! In any case, if it's impossible to have a base of 8 with 5 parts, I will settle for a competition with 4 parts (usually, the competitions organized are of 4 parts). If the base of 8 is validated by you (5 or 4 parts), I will continue this weekend with bases of 10, 12, and 14, which would allow all the Triplets starting from 8 without exception. Good luck Best regards Dodo   https://mon-partage.fr/f/llJDsj4Z/

DodoMichelle · Answer

Hi,  After multiple combinations in 5 parts based on 8, I still have some duplicates. I therefore give up on creating a file with 5 parts. These days, I will try to set up several bases  (8/10/12, and 14) in 4 parts so that you can then develop more easily and consolidate the overall file, if you agree, of course! Before that, I would need you to validate my base 8 in 4 parts. Thanks and have a good weekend Best regards Dodo

via55 · Answer

Hello Dodo  In 4 parts, there are duplicates with opponents; I've highlighted in red the first ones I saw (for example, P2 in part 1 plays against P9, whom he meets again in part 3, and against P1, whom he meets again in part 4). I haven't continued checking until the end. https://mon-partage.fr/f/OxRvG3gp/ I don't know if it's possible to find a draw without duplicates, and I haven't been able to build the macro to do it so far.  Have a good weekend as well. Best regards Via  -- "Imagination is more important than knowledge."    A. Einstein

DodoMichelle · Answer

Good evening Via,  I can't find any solution without duplicates and my head feels like a mess... I'm wondering if we should go back to the file you built below in 4 parts (definitively abandoning 5), there was just a problem with duplicates in every other part. Keep the 64 fixed shooters and 128 random pointers, even if there are a few duplicates, they will be "random" themselves. Best regards Dodo https://mon-partage.fr/f/XKCPSyb7/

via55 · Answer

Good evening Dodo  With formulas, I can't come up with anything truly convincing An attempt with random draws for each of the 4 parts could lead to duplicates. The greater the number of players, the lower the risk of duplicates, in principle https://mon-partage.fr/f/4HQIqsPv/  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  I completely agree. In any case of constructing a file of this type, even randomly, there can inevitably be some duplicates. We have to deal with it, so it's no longer a problem! The file is in protected mode, I am in read-only, without the ability to run macros. Is it possible to isolate the 64 Shooters (so that they do not meet twice) and make a random draw only among the remaining 128 Scorers? Best regards, Dodo

via55 · Answer

Hello Dodo  Check if it works for you https://mon-partage.fr/f/T7TjdNzo/  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  I have just finished some "tests": apparently, it's perfect, no bugs, with 64 triplets, no duplicates in about fifteen simulations. I will continue to "dig"....  A few details: - When there are no players registered, 0 in part 4 in the list tab. - I no longer have the ranking by clubs (if you can restore it).  Can I send you my other files (doublets among others that I had already submitted, we have limited ourselves to triplets), knowing that I'm not in a hurry! In the meantime, a big thank you for your work. Best regards Dodo   https://mon-partage.fr/f/Fmbikff6/

via55 · Answer

Good evening Dodo  I'm surprised there are no duplicates I'll check the little details on Wednesday Send me the other files  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  Is it possible, through formulas, to color the cells that are duplicates, in order to possibly run the "draw" macro again to limit them, or at least to manage them? (I couldn't find a way!). I will send you the other files during the week. Best regards Dodo https://mon-partage.fr/f/CImCwSYy/

via55 · Answer

Good evening Dodo  I had the same idea but through macros https://mon-partage.fr/f/rbxYY7rK/ For my part, there are still too many duplicates We should be able to do a random draw for each teammate and opponent while removing already occurred ones, but it would require completely redesigning the file and the macros (we need to record all the compositions already made and compare them) but I fear that we may end up in loops that will run for a long time, if not infinitely.. I am currently unable to come up with something that could work  Best regards Via

DodoMichelle · Answer

Good evening Via,  Indeed, I just did some manipulations and ended up with a lot of duplicates each time! Your "duplicates" macro is great. I'm going to take the bases 10, 12, and 14; if I manage to do something, we could create a file this way starting from 30 players if base 10 is okay, otherwise starting from 36 if base 12 is okay, below that, only winners against winners. What do you think? Best regards, Dodo

via55 · Answer

Re,  Yes, but winners against winners means that the partners remain the same then? On my side, I continue to look at a macro level to see if it's possible to have random draws for part 1, for parts 1, 3, 4: distribution of shooters by formula to avoid duplicates (like in the last file sent) random draw of partners by removing for each pair of teams, if already a partner for the 1st team and if already a partner or opponent for the 2nd team while limiting the number of removals (to avoid slowness or blocking), which I think should not completely eliminate duplicates but reduce their number   Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  To confirm: Yes, winners against winners, we do not change partners, whether in doubles or triples, regardless of the number of players, there are many competitions with 4 matches in this way. However, what I am trying to set up is a more friendly version among friendly clubs. For the record, I am in charge of organizing meetings between friendly clubs, in addition to the official competitions (so a fairly varied range of combinations). I deduce from the problems encountered that it may not be possible to organize random draws with fewer than 30 or 36 teams (doubles or triples)! I haven't had the time yet to look at the bases 10, 12 and 14. Best regards Dodo

via55 · Answer

Hello Dodo  Would you have a first version of the unprotected file so that I can look at the winners against winners formula? Because I can't delete, add, or copy the sheets in all the latest ones sent We now need to think first by teams, which means designing the file differently  Best regards  Via

DodoMichelle · Answer

Hello Via,  I'm currently looking for a base 12, I still have at least one duplicate, I'm giving up for now, I'm going to watch rugby! Attached are examples that I hope will work. Good luck Best regards Dodo  https://mon-partage.fr/f/rgCo9Go3/  https://mon-partage.fr/f/wgZXUJyL/  https://mon-partage.fr/f/t28jnheU/

via55 · Answer

Re  I was thinking about the TOURNAMENT files with random drawing  Because in the latest files sent the teams are composed at registration  What you want is indeed something similar but with a draw for the teams among 1/3 shooters and 2/3 pointers? for a maximum of 192 players, that is, 64 triplets max? with the list of players as in TOURNAMENT and check the participants?  Or would you like to adapt the formed Triplets file to draw the teams?  I'm only dealing with the triplets for now; we'll see about the rest later

DodoMichelle · Answer

Hi, Yes, there’s definitely enough to do with the triplets! It’s indeed a random draw with 64 shooters and 128 pointers for 192 players. I hope what I'm sending you is good this time?   https://mon-partage.fr/f/A3RpDO3d/  https://mon-partage.fr/f/xcQtHXHE/ Best regards, Dodo

via55 · Answer

I can access everything in the first one, but not in the second Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Re, I'm really bad at this, I can't remove the password from the VBA (dodo). Best regards Dodo

via55 · Answer

It's not the VBA password that's causing the problem, the workbook was also protected by a password, I just realized it, fortunately with the same password, I was able to unprotect it.  Best regards Via   -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Re,  I don't know if it might be useful, I've finished a base 12 that I would like you to check if you think it's useful! Best regards Dodo  https://mon-partage.fr/f/dyfvf66H/

via55 · Answer

Hello Dodo  I will check your base 12 Meanwhile, here is the triplettes file by teams to test https://mon-partage.fr/f/RQItOH5m/  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  First of all, thank you. From the design, I deduce that the draw is done randomly at the beginning for the first part, the teams do not change for the remaining 3 parts, but winners against winners. The reset is done by pressing the "make the draw" button, the tabs are not blank (see below), the goal average should be inverted with the number of matches won (not a big deal). There is no ranking by clubs (I think this is intended in this prototype!). I just did a test right now and encountered a bug, whereas with 64 triples, I noticed nothing!   https://mon-partage.fr/f/UB83j8UT/  https://mon-partage.fr/f/kQaVFVt8/  Best regards Dodo

via55 · Answer

Good evening Dodo  Exactly I will look at the bugs tomorrow evening  For the base 12, I have, as in the previous one, created a table of partners and opponents via the macro, and there are duplicates https://mon-partage.fr/f/kxtxJBFB/  If it were a total scrum, it would probably be possible to avoid duplicates, but since we separate shooters and pointers, I don't know if there is a solution without duplicates  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

via55 · Answer

Hello Dodo  https://mon-partage.fr/f/NBlHWq1z/ Bugs fixed for display Clearing scores on reset in List  What is not a bug: the malfunction if there is an odd number of triplets because it is not possible to match winners and losers  I didn't understand what you meant by goal average I copied the sheet from previous files which should have worked correctly  When the file is fully operational, we can make a clubs version  Best regards Via     -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  Sorry, but I tested with 20 triplets, everything was fine, then with 18 and it bugged (see attached file). What I meant to say is that there was a mix-up between the points conceded and the matches won in the Classt tab. I’m going to wear out your patience! Best regards Dodo?    https://mon-partage.fr/f/8Q2IGYWd/

via55 · Answer

Re  No bug, it's me who expressed myself poorly, it's the number of pairs of triplets that must be even, with 20 triplets that makes 10 pairs so in the next round there are 5 winners against 5 winners and 5 losers against 5 losers but with 18 triplets that makes 8 pairs of teams playing against each other and one team with no opponent, the formulas cannot handle this case  For Ranking yes I hadn’t noticed, the formulas in columns C and D need to be reversed  Best regards  Via   -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  If I understand correctly, for the triples competitions, winners against winners, there must be a multiple of 4, such as 8/12/16/20/24/28/32 teams, etc. However, in the case of 20 teams, in the second round, there will be 5 winners, so in the third round, there will necessarily be a winner who will face a loser from the second round? In my opinion, in all cases, in the third round, there is an odd number of winners! Best regards Dodo

via55 · Answer

Good evening Dodo  With 20 teams in each match, there are indeed 10 winners and 10 losers, and the winners meet each other in the next match and the same for the losers Or is there something I'm missing?  Best regards Via    -- "Imagination is more important than knowledge."    A. Einstein

DodoMichelle · Answer

Hello Via,  As an example below, the format of a winners' tournament with 4 winning parts: with 20 triplets, in the 3rd match, there are 5 winners, so the 5th can only meet a loser from the 1st or 2nd match. Once again, I fear I'm not very clear!   https://mon-partage.fr/f/UqpeuE0q/ Best regards Dodo

via55 · Answer

Hello Dodo  Okay, we don't use the same terms for winners-losers. When I talk about winners, I mean for each match (regardless of whether the player won or lost the previous match). So the formulas in the file do indeed match the winners of the previous match against each other, and it is certainly possible that a loser from the previous match is among them. See the table I included below yours. Does this approach pose a problem? https://mon-partage.fr/f/HLiZ6x9j/  Best regards Via

DodoMichelle · Answer

Hello Via,  I have no problem with how you distribute the winners. In fact, with 18 teams, there is one winner missing to meet team 18 in the second part of your table? For this type of event, we need to have a multiple of 4 during registrations, to have an even number in the second part, so: 4/8/12/16/20/24, etc....... I will continue testing this evening, but I am optimistic and confident!!! Best regards, Dodo

DodoMichelle · Answer

Good evening Via,  I ran some tests, and it seems there are no bugs in your file, which works perfectly! I am currently occupied with some work at my place, which makes me less available for my issues. I have reordered the formulas for the conceded points and the won games (Classt tab), however, no matter how I try to sort with the won games (>number, isn’t < the one that comes first?). I will continue testing. Have a good weekend Best regards Dodo https://mon-partage.fr/f/6ImCPvvj/

via55 · Answer

Hello Dodo  I don't understand (the number, is the < in first place?). The ranking is indeed in accordance with the goal average  Best regards Via  -- ""Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  I'm talking about columns J to Q, where the ranking is based on the number of matches won, then points. It's always the teams that have won the least number of matches that are ranked first, despite the sorting. However, columns T to AA are correct. (You need a decoder to understand me!!!) Best regards. Dodo

via55 · Answer

Good evening Dodo  Oh yes, I hadn't noticed! There were still some inverted formulas in the columns That should be good now https://mon-partage.fr/f/JrWb43L7/  Best regards Via

via55 · Answer

Hello Dodo  The last file is not the right one (it has links to other workbooks) Here is the correct one (at least I hope so!) https://mon-partage.fr/f/eikgmuF5/  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

via55 · Answer

Re  When I reopen the workbook, I have links again! I just checked and they already existed on the file you sent me I tried to break the links but it doesn't work It's enough not to update the links for the file to work anyway, but still!  -- "Imagination is more important than knowledge." A. Einstein

via55 · Answer

Here is the same file for clubs https://mon-partage.fr/f/1EvgdVOB/  I noticed while doing it that in the previous one, during the update of the 2nd table in the ranking with your macro associated with the Goalaverage button, the ties were no longer taken into account, which could distort the ranking by clubs I have therefore reintegrated in this one in column T (cell T4 and below) a formula to take them into account It might be necessary to put the same formula in the previous file  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  There is no rest for you! I'm looking at the work done, and I'll keep you updated if I manage to understand everything!!!!!!!!!!! Thank you very much. Best regards Dodo.

DodoMichelle · Answer

Good evening Via,  While being quite busy (house renovations), I tried your 2 files which seem to be working wonderfully well! I copied the formula for the exæquos into the first file, perfect! Sorry to have made you "sweat" over these files, and this is not over yet....(you remember there are others to create!!!!) Once again, a big thank you. Best regards Dodo

via55 · Answer

Good evening Dodo  Well, if everything is working, that's great I’ll wait a little longer for you to do more thorough testing and we’ll talk about the next ones  Best regards  Via   -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  I haven't encountered any bugs after various tests. There is just the clubs ranking showing N/A if no players are registered. I will continue testing. Best regards Dodo

via55 · Answer

Good evening Dodo  You can handle the N/A with IFERROR(... as I did elsewhere  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  I've finalized your file (Remove protections, except for the workbook, hide macros, remove the N/A, do other tests). I encountered no bugs! If you want to take a look? In the meantime, really a big, big thank you......... We'll keep in touch regarding the next steps. Best regards Dodo   https://mon-partage.fr/f/11ioEsRe/

DodoMichelle · Answer

Good evening Via,  I have put a protection on all the tabs, macros and VBA with the usual password. When I conduct tests, some tabs are not protected anymore, apparently not always the same ones, but often: list, recap, class teams, and clubs! I checked the different modules (1 to 7, I don't find anything abnormal), however in thisworkbook, there seems to be a password??? From time to time, there's a bug in the ClassementE macro below (Selection.EntireColumn.Hidden = True appears in yellow), but it doesn't interfere with the functioning.  That being said, no bugs in the tests.  Private Sub ClassementE()   'Ranking Macro  ActiveSheet.Unprotect "dodo"  Sheets("Classement Equipes").Select   Columns("R:R").Select  Selection.EntireColumn.Hidden = True  ActiveSheet.Protect "dodo"  https://mon-partage.fr/f/h4MUwuuY/  Thank you again if you can still spare some time! Good luck  Best regards  Dodo

via55 · Answer

Good evening Dodo  Yes, it's strange  There's nothing in ThisWorkbook  I apparently solved the problem by putting a macro in the worksheet of each sheet to reprotect upon disabling the sheet  https://mon-partage.fr/f/6hdznbnl/  As for the E ranking bug, I bypassed the line that wasn't really useful (hidden in column R) and was causing the bug  Best regards Via  -- "Imagination is more important than knowledge."    A. Einstein

DodoMichelle · Answer

Good evening Via,  Thank you, the two issues are well resolved. However, despite the passwords on the macros and VBA, when I display the projects, nothing prevents me from modifying the macros. I thought it was necessary to enter the password in VBA to access the macros? What is the point of hiding them if we can access them otherwise, or is there still a misunderstanding on my part, as often! Best regards Dodo

via55 · Answer

Re  In the VBA editor - Tools when you enter or modify the password, do not forget to check Lock the project for viewing  Regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hi again,  On top of that, I'm losing my memory! This time, everything works. Thank you and good night. Best regards Dodo

DodoMichelle · Answer

Good evening Via,  Are you okay with it and do you have time to take on my other files in the same format as last time, which works perfectly. Have a nice Sunday Best regards Dodo

via55 · Answer

Good evening Dodo  Little by little I should be able to make it Refresh my memory, what are the other files to be processed and especially send me the latest versions you have that work, I have so many with the various attempts that I no longer know which ones to take  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via, I’m also a bit lost with all my files! I’m forwarding you the last file (the random draw takes place in the first round, with the teams remaining unchanged in the other three matches).  Could you create the same file with a random draw for each match, which means the teams will change in each match (there may be redundancies if there aren't many participants)? For example: T1 plays all four matches with P2 and P62 in the first file; in the one to be created, T1 and the other shooters change pointers for each match. I hope I'm being clear? We’ll look at the others later. Have a good evening Best regards Dodo https://mon-partage.fr/f/Ti2VBAWS/

via55 · Answer

Hello Dodo  To test: https://mon-partage.fr/f/pEdXfIBN/  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  What speed and efficiency! It's simply "perfect". Moreover, we can display the 4 parts at the beginning of the contest since the random draw occurs on the first click! I've done tests and checked many parameters, and I found absolutely nothing abnormal. However, in the two ranking tabs, when there are ties (e.g., 1st, 1st, and the 3rd is 2nd), I don't know if you can modify that? It's just a detail that ultimately doesn't change anything. Thanks again Best regards Dodo   https://mon-partage.fr/f/oCgG8Ph1/

DodoMichelle · Answer

Hello Via,  I tried to create a ranking considering the ties in the player ranking tab, column AB, but the ranking is reversed???????, I have to do another one! I don’t know which one?   https://mon-partage.fr/f/LMcpMOBs/ Thanks for your reply. Best regards Dodo

DodoMichelle · Answer

Re,  I just found a semblance of a solution but, in my opinion, it is completely illogical and ridiculous (I reversed the original ranking), apparently the result is good, I am sure there is another way but it is beyond me! Best regards Dodo  https://mon-partage.fr/f/rMBqgCb9/

via55 · Answer

Hello Dodo  I am completely overwhelmed right now, I am trying to look into this as quickly as possible  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

via55 · Answer

Hello Dodo  https://mon-partage.fr/f/LQunVmhD/  I completely reorganized the ranking by goal difference, initially managed by your macro I created a ranking based on the following criteria number of games x 10,000 + 100 x goal difference, to which we add the absolute value of the smallest goal difference (since some are negative) to obtain a positive value or = 0 + points + (100 - conceded points)/100 then rank and rank without ties everything is handled by formulas like the ranking 1, no need for a macro anymore  using MFC, players whose ranking changes with the goal difference are highlighted in orange  there's no longer any need for a special club sheet here (since it's an individual ranking and not by teams) they are managed in the same player ranking sheet  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via, I feel awkward, I know you’re overwhelmed and that I’m not the only one bringing you problems. Don't feel obligated to respond to me quickly, even though I really appreciate it! I didn’t understand everything in your new file (I need to dig into it a bit more). However, I have highlighted in red what I think are not the correct rankings. I also included a file that still needed to be cleaned up (I had also deleted a rankings tab and I was so proud to have found the formula for tied scores!!!!!) I can never thank you enough for what you do for me. Good luck Best regards Dodo  https://mon-partage.fr/f/VQnY41lZ/  https://mon-partage.fr/f/U89XpxsX/

via55 · Answer

Re  I think my formulas in column AB and following are correct (calculations made on the data from ranking 1) I "simply" forgot to bring in the correct goal average for display in column AA, so in AA3 put the formula:  =INDEX(Q:Q;MATCH(ROW()-2;$AD:$AD;0)) and stretch it down Everything should match now (I'm crossing my fingers)  Your formula is correct and I find the same ranking using my method ☺  Best regards Via -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via, I applied your formula and tested it with 192 players, the ranking was correct. I just did a test with 16 triplets. In the 1st game, in the list tab, there are the results of the other games. The other tabs are correct. We'll see, I'm not sure if it's an issue with the final result. Best regards Dodo   https://mon-partage.fr/f/vzBpiJux/

via55 · Answer

Good evening Dodo  In the list tab, the tables in columns AO to BL determine the winners and losers based on the points entered in sheets part 1, then part 2, etc., and compose the pairs for the next part. Therefore, these tables change as the parts progress and do not affect the point counting.  You can hide all these columns in the List  Best regards  Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  Indeed, everything is fine. After multiple tests, there are no bugs, the file works perfectly. "Silly" question: is it possible to modify the formula in the 2nd part of the list tab in column AV, so that the last winner faces the 1st loser for example if the number of teams is a multiple of 2 and not 4 ????? so that this file can be used with an even number of teams 8/10/12/14/16/18 etc... instead of 8/12/16/20/24 (multiple of 4) etc... Best regards Dodo

via55 · Answer

Hello Dodo  There are no silly questions ☺ Revised in the sense you wanted https://mon-partage.fr/f/OZ7gUFcF/  Best regards Via   -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  You are really amazing!!!! I modified the formulas in columns BC and BJ (there was an inversion between AX and AY, BE and BF), I think it’s good now. I am very satisfied that the file can work in 100% of the cases. Please check. If you confirm, I will modify the others. Best regards Dodo  https://mon-partage.fr/f/I7q5xwNK/

DodoMichelle · Answer

Hi,  By the way, what is the purpose of the cell formula AM3 in the ranking tab? Best regards Dodo

via55 · Answer

Re  Yes, you were right to correct it, I don't know what I was doing when I copied the formulas from one table to another! As for the formula in AM3, it was a test for the formula in AB that I should have deleted afterwards and that I forgot to do!! Honestly  Best regards  Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hi,  Okay, really great the table and simple! I approve and I'll look to modify the others. I'll forward you my other issues without urgency!!!!! Thank you and good luck. Best regards Dodo

DodoMichelle · Answer

Good evening Via,  Sorry to bother you, but while trying to secure your file, I'm still having trouble with the macro protection (Bug when launching the macros: highlighted in yellow in VBA: ActiveSheet.Unprotect="dodo"). I can't see the reason at all. If you have a moment!!!!!!!! Best regards Dodo  https://mon-partage.fr/f/AXLWprNO/

DodoMichelle · Answer

Hello Via, Please disregard my previous message (error in ActiveSheet.Unprotect and Protect with an extra = sign). Now, when I click on the tabs list, recap, and ranking, at least 2 of them have the tab protection removed each time!!! not if I press the macro button. Best regards Dodo.  https://mon-partage.fr/f/ID7VjuQu/

DodoMichelle · Answer

Good evening Via,  Just to let you know: the issue is resolved! In the list, summary, and ranking macros, I placed ActiveSheet.Protect "mdp" at the beginning and at the end, and it works (my sheets remain protected), which is great, but I don't understand why. Thank you and have a good evening. Best regards Dodo  https://mon-partage.fr/f/0W9us6Pb/

via55 · Answer

Good evening Dodo  Well, that’s a relief ☺  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  The last file you created for me is very well done. Could you, please, if you wish and when you have time, do the same with: 1) Random draw for the first round (64 Shooters and 124 Scorers: the teams do not change after the first round). 2) Teams formed at the start, so no random draw to form the teams. It shouldn't be a priority for you, of course. Thank you in advance. Best regards, Dodo  https://mon-partage.fr/f/2aefMs1Z/

via55 · Answer

Hello Dodo,  Here are the two files https://mon-partage.fr/f/wrcVmE7Q/ https://mon-partage.fr/f/Zbg3dEWo/  Have a good weekend Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  Really great! It’s exactly what I wanted. Thanks again. Have a nice week. Best regards, Dodo

via55 · Answer

Good evening Dodo  Thank you, likewise Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  I'm trying to take back your files to create identical duplicate files, but I'm stuck on the formulas in column AE of the list tab! (I thought I had copied the same ones while considering the columns and the number of duplicates, but???).   https://mon-partage.fr/f/N9nmk5qc/ Duplicates  https://mon-partage.fr/f/hajK4xy3/ Validated Triplicates  If you can spare some more time? Thanks in advance. Best regards Dodo

via55 · Answer

Hello Dodo  The formulas from P should not be touched! nor the other sheets It's simply the formulas in column K and E4 to be adapted to have a draw among the pointers that goes to Pointer 1 After that, I've just hidden the Pointer 2 columns in the List and in each Partie sheet https://mon-partage.fr/f/M1ijmzpc/  Best regards Via  -- "Imagination is more important than knowledge."    A. Einstein

DodoMichelle · Answer

Good evening Via, It's fine for 128 players, but what I was thinking of doing is still with 192 players, making 96 pairs, which means 96 Shooters and 96 Scorers. That's why I modified the tabs! Is that possible? I'm sending you my file which is still being modified. Thank you Best regards Dodo  https://mon-partage.fr/f/QnsCOSrc/

via55 · Answer

Good evening Dodo  Oh yes, I didn't realize! As usual, it was 1/3 shooters and 2/3 pointers So here is the revised file https://mon-partage.fr/f/ATFEceiJ/  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  Thank you for the file. I don’t know if I will reach out to you soon, however, I wish you to have a joyous holiday season. Best regards Dodo

via55 · Answer

Good evening Dodo  Thank you, same to you ☺  Best regards Via  -- "Imagination is more important than knowledge."    A. Einstein

DodoMichelle · Answer

Hello Via,  Here I am again! Could you please, based on your last file (below), create: - A file with random draws for only the first part (the pairs do not change in the other parts). - A file with random draws for each part. Actually, just like what you did for me with the triplets. Best regards Dodo  https://mon-partage.fr/f/QZR9lHVt/

via55 · Answer

Good evening Dodo  Here are the 2 files https://mon-partage.fr/f/zTmj3FLb/ https://mon-partage.fr/f/BU0F7gHE/  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  Thank you for your quick response, however, none of them are working for me! 1) The recap and classt tabs are not updating, nor is the number of participants in the list tab. 2) The draw macro is not working (I don't have the path it suggests).  Have a great holiday season and take care of yourself.  Best regards, Dodo

via55 · Answer

Hello  My mistakes corrected: https://mon-partage.fr/f/FvmDzHao/ https://mon-partage.fr/f/uctwDKAM/  Have a nice holiday season as well  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  I wish you and your loved ones a happy and prosperous New Year 2021, with health, happiness, etc... Let's quickly forget 2020, may this new year bring everyone the serenity to live better together. Best regards Dodo

via55 · Answer

Hello Dodo  Thank you very much for your wishes, now it’s my turn to wish you the best, and especially health, for you and your loved ones. Let’s hope that 2021 will make us forget 2020 as you say!  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  I'm reaching out to you again: the first file of doubles is OK  https://mon-partage.fr/f/zEs9Xcjo/  The other 2 are not OK: no reset, still 192 registered players, even if the list is for 16 for example, etc.  https://mon-partage.fr/f/swihtVQU/  https://mon-partage.fr/f/WPidFknO/  In triples, everything seems fine to me If you have the time? Thank you Best regards Dodo

via55 · Answer

Hello Dodo  Hoping this time will be the right one! https://mon-partage.fr/f/QxSvbvd0/ https://mon-partage.fr/f/zD6wq5UT/  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Hello Via,  A few more bugs that I don't know how to fix:  -If there are no players, cells E3 and E6 stay at 192 and cells AO7 to BN8 are not empty. -If there are 192 players, only 64 doubles are taken into account in all tabs. For now, I haven't gone further. Thanks in advance Best regards Dodo.

via55 · Answer

Hello Dodo  first draw: https://mon-partage.fr/f/7C2oFBKX/ draw for each round: https://mon-partage.fr/f/rNGC2OGY/  but I noticed that in this 2nd file, even with random draws, several shooter-pointer pairs appeared in 2 rounds, so I made a new file with a random draw for round 1 and then by shifting the pointers formulaically for the following rounds, the same pair cannot reappear: https://mon-partage.fr/f/vm1MWYRm/  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  I just checked the first file, I didn't find any bugs, just a simple remark that I believe doesn't interfere with the functioning: The cells AT/AU, BA/BB, BH/BI are empty.  https://mon-partage.fr/f/NgIGODAP/  I will check the other one. Thank you. Best regards Dodo

via55 · Answer

Good evening Dodo  Yes, when you delete all the entries, there remain, due to the initial formulas, 2 non-empty rows in columns AT and beyond, but this has no impact since nothing happens until you check the entries and perform the draw  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  It's me again! I just realized (I was caught up with something else) that in the files, the rounds are incremented even if the scores from the previous round are not recorded. For example, in the attached file, the names for the 4th round appear even though the 3rd has not been played! Is this perhaps not an issue? Thank you for your feedback (there's no rush). Best regards Dodo https://mon-partage.fr/f/d9Hgwhjn/

DodoMichelle · Answer

Good evening Via,  I have a new problem on another topic! See attached file: If I have 3 consecutive cells with the letter A (regardless of the cells), cell E27 displays 3. After many attempts, I've never been able to achieve this... If you can, once again, help me? Thank you Best regards Dodo  https://mon-partage.fr/f/ImDzVjxR/

via55 · Answer

Hello Dodo  It's normal, these are the formulas, but when the results of a match are entered, the correct opponents display properly  Best regards Via  -- "Imagination is more important than knowledge." A. Einstein

via55 · Answer

For attendance, using an intermediate column (to be hidden later if necessary) https://mon-partage.fr/f/PYt3O2PW/    -- "Imagination is more important than knowledge." A. Einstein

DodoMichelle · Answer

Good evening Via,  How simple it is, after the work has been done by someone else!!!! Thanks once again. Best regards Dodo

DodoMichelle · Answer

Hello Via,  What should I do: 1) If I think my issues are resolved? 2) If I bring up another issue later and want to propose it to you?  Have a good day and take care of yourself. Best regards Dodo

via55 · Answer

Hello Dodo

1) You had already marked the issue as resolved, so there's nothing more to do

2) For another request, please open a new post and feel free to send me a private message to let me know about this post in case I haven't seen it

Have a nice day as well

Best regards
Via

--
"Imagination is more important than knowledge." A. Einstein

Petanque competition file

206 answers

Canon pixma printer not detected via livebox 7

No sound on my pc

Windows button not working anymore

Unrecognized external floppy disk drive

Unable to open xsl and xslx files

Convert ts to m2ts

No internet connection possible

Storage space running out soon

Asus tuf gaming b650-plus wifi dram issue

Potentiometer not working