Petanque

Hello momo

Your story about shifting is not clear
Give an example on an Excel sheet of a 1st draw and those you expect for the following ones and post your example on mon-partage.fr by creating a link that you copy and come back to paste here
Doublettes or triplettes competition? How many games?

Best regards
Via

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

Hello VIA 55
I’m back from vacation
Thank you for taking care of the PÉTANQUE file
New to computers, I’m not good at creating a hyperlink
and pasting the link (here) doesn’t work
If possible, please detail step by step

Best regards

Hello

To upload a file:
1) Go to https://www.cjoint.com/
2) Click on [Browse] to select your file (max 15 MB)
3) Scroll down to click the blue button [Create Cjoint link]
4) After a few seconds, the second page will display, with the link in bold; right-click on it and choose "Copy link"
5) Go back to your discussion on CCM, and in your message, "Paste."
=> See the guide https://www.commentcamarche.net/faq/29493-utiliser-cjoint-pour-heberger-des-fichiers

Best regards
Via

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

Hello VIA 55
Thank you for the explanations, I think I succeeded

https://www.cjoint.com/c/HHbjlLT7jVW

Best regards

Hello Momo

Yes, it's okay,

On one hand, it's impossible to make sense of your VBA, there are too many modules, many of which are empty, we should first clean it up a bit
I don't understand what the Tirage sheet is for and why its draws have nothing to do with those in Tour 1, Tour 2, etc., can you explain that to me?

On the other hand, if I understand correctly, it's in the Tirage sheet that you want to make a report
The numbers in columns A to F should be reported to columns H to M with the offsets you mentioned?

Your thing is a bit of a complex system, there must be a way to simplify it, but for that, we need to understand the logic
Would a first random draw among the registered participants followed by shifting for each round according to your indications be sufficient?

Best regards
Via

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

Your thing is a bit of a maze, there must be a way to simplify it, but for that, we need to understand the logic.

Would a first random draw among the registered participants, then shifting for each round according to your indications, be enough?

Yes, that would greatly improve it.
Thank you.

So let's clarify the possible process:

1) In sheet 1, you enter the participants (max 40 per category shooter, etc.)
2) A random draw mixes the participants in each column to obtain the teams for round 1 in the sheet for round 1
3) The fields are randomly drawn for this round
4) The teams for round 1 are shifted according to your instructions to obtain those for round 2 (this can probably be done using formulas without macros)
5) We keep the fields in the same order as the first draw; since the teams are shifted, they shouldn't end up too much on the same one, or we could redo a random draw of the fields with the risk of ending up on the same field for some?
6) We apply the same procedure for round 3 and then for round 4

In the case of triplets, we take care of the 3 columns, in the case of doubles only the first 2 since the 3rd is empty?

Do you agree with all of this?

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

So let’s clarify the possible approach:

1) In Sheet 1, you enter the participants (max 40 per category shooter, etc.)

2) A draw randomly mixes the participants from each column to obtain the teams for round 1 in the round 1 sheet

3) The fields are numbered from 1 to 20 across the 4 matches since the players are shifted with each draw

4) The teams for round 1 are shifted according to your instructions to obtain those for rounds 2, 3, and 4 (this can likely be done with formulas without macros)

5) We keep the fields in the same order as in draw 1; since the players are shifted, they shouldn’t end up on the same field (that’s the purpose of the shift). The draw should take place in ROUND 1, ROUND 2, ROUND 3, ROUND 4 (The role of the FIELDS sheet is visual color to avoid having 2 or 3 women playing together, UNLESS there are more women than fields or 3 scorers and 3 shooters)

6) We apply the same procedure for round 3 and then for round 4

In the case of triplets, we handle the 3 columns; depending on the number of players, it’s possible to obtain doubles

In the case of doubles, also 3 columns; it’s possible to obtain triplets depending on the number of players

In cases where the number of players exceeds 72, i.e., 12 fields, team 13 and subsequent teams will occupy the available fields

Another constraint: some players do not participate in all 4 draws
Good luck

Re,

1) For the draw, we can create a mixture for each column on another sheet.
Incomplete rows will give doublets.
This gives Round 1.

2) However, I don't see how to manage for the other rounds if some players no longer participate. We cannot just shift columns; we would need to establish a new list and proceed with a new draw, but there's no guarantee that teammates from the first round will end up together!
And if we don't shift the first column either, the players in that column who played in doublets, if there's a mix of triplets and doublets, will always end up in doublets.
This is really a headache!

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

Here is the draw I arrive at in sheet T
https://mon-partage.fr/f/I8FXzhgO/
See before my comments in my previous message

On the other hand, before proceeding, you need to clear your file of unnecessary modules and macros; it is becoming too heavy, over 14,000 KB!!!

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

Good evening VIA 55
RANDOM DRAW
1 In the sheet TOUR 1 column C 13 find the last non-empty cell, copy it to the TIRAGE sheet, paste in A4
D13 B4 -E13 C4-F13 D4-G13 E4-H13 F4
This determines the number of lots
In the TERRAINS column, create a continuous numbering

DRAWING CONDITION 2
In column A4, search for the last non-empty cell
CTRL+C CTRL+X and CTRL+V in column H 4 copy the rest of the cells in column H5 (offset 1-2-3 3-1-2
The CTRL +X facilitates the copying of the rest of the non-empty cells
at the end of the cycle restore the RANDOM DRAW sheet
Do the same for
DRAWING CONDITION 3 and 4
After control and correction if necessary the sheets will be directed to
TOUR 2 -3- 4 special paste with link ??
Regards momo

I don't understand anything about what you're doing
You have a very clear idea and you stick to it, without answering my questions
I don't understand anything about your stories of condition draws 2, 3, and 4!
In my opinion, you're complicating things and as I said, you're turning it into a real mess that I don't grasp the logic of

I'm throwing in the towel!
If someone understands better than I do, they can take over

Sorry
Regards