Group draw as specific repetition

[hpernaf]

Hi folks,

I would like help with a small project that I am creating for a name draw worksheet (groups of three).

I need to draw groups of 3 people from a list and insert these groups one below the other as I click the button to perform the draw.
1.PNG

A detail: Names cannot be repeated in the draws. In cell B3 I entered a value, which I would like the names to repeat only after this amount of drawings.

I did a search on the internet, but I didn't find anything like that for group draw, I just found the use of the RAND function for group draw.

Could someone help me with this task?

Thank you in advance

[AliGW]

This will require VBA - shall I move the thread for you?

[hpernaf]

Yes please.
Sorry for posting in the wrong location.

[Marc L]

According to your attachment as a VBA beginner starter :

Sub Demo1() 
    Dim V, R&, N&, W 
        V = Sheets("Names").UsedRange.Value2 
        Randomize 
    For R = UBound(V) To UBound(V) \ 4 Step -1 
        N = Fix(Rnd * R) + 1 
        W = V(R, 1):  V(R, 1) = V(N, 1):  V(N, 1) = W 
    Next 
        [E2].Resize((UBound(V) \ 3) * 3).Value2 = V 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[hpernaf]

According to your attachment as a VBA beginner starter :

Sub Demo1() 
    Dim V, R&, N&, W 
        V = Sheets("Names").UsedRange.Value2 
        Randomize 
    For R = UBound(V) To UBound(V) \ 4 Step -1 
        N = Fix(Rnd * R) + 1 
        W = V(R, 1):  V(R, 1) = V(N, 1):  V(N, 1) = W 
    Next 
        [E2].Resize((UBound(V) \ 3) * 3).Value2 = V 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Perfect Marc!
Thanks for your help.

Would it be possible to insert only 3 names drawn at a time in the list?

Example: I click on the button and 3 names are added to the list (E2: E4). I click the button again and 3 other names drawn are inserted in the list below the first draw (in E5: E7). And so on, repeating the names only according to the value of the round entered in cell B3.

Thank you.

[Marc L]

The easy way to avoid any repetition and a 'gas factory' code is to draw 3 x B3 cell at a time so 24 names …

[hpernaf]

How would that work?

[AliGW]

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

[Marc L]

Exactly the same beginner starter just allocating obviously the first 24 names from the array rather than all the array !

For each new 24 names draw, column E should be cleared or the new draw is allocated after the last one ?

[hpernaf]

Thank you!

[AliGW]

Administrative Note:

Sorry, but your post does not comply with Rule #6 of our Forum RULES:

Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction, then send a private message to them asking for clarification.

All Participants:

Please do not post a reply in a thread where a Moderator or Administrator has requested an action that has not yet been complied with (e.g. title change, code tags requested, etc.). Thanks.

[hpernaf]

Sorry about that.

[AliGW]

Where is the cross-post link I have requested? You still need to provide it.

[hpernaf]

https://www.mrexcel.com/board/thread...ition.1138401/

[AliGW]

Thank you.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.