Random Pick 4 column of datab but keep 2 cells tied together.

[Thupp]

I have 4 columns of names in 13 rows in a golf program. I am trying to put together a macro that will randomly pick 2 man teams to be paired with another 2 man team. Occasionally, we have to pair a threesome together and I would like that team to be in the first pairing. Anyone up to this one? I have been trying to figure this out for sometime, but am new to Excel and all my research as come up empty.

[MickG]

With you Data in "A2 to D14" this code should create you pairs of pairs starting "G2".

Please Login or Register  to view this content.

Regards Mick

[Thupp]

Mick,

Does not work as planned. Substituted my range, but had problem. I do not always have every golfer show up, so there are blank spots. Always at least a 3some in the random draw group. My pick range is already a random pick of current players that go to Range I3:L15. This range is set up to determine who is on team for the money. I'm trying to set up another macro to take that range (I3:L15) and randomly pick another group keeping I3 and J3 together but pairing them with any other pairing except K3 and L3 and so on. (Teams are set up in rows). We do not want the teams playing for money playing within the same group. (Not that we don't trust anyone, but it is golf)! I can get your macro to work if I limit the range to the ones that have players, but I think the blank rows generate an error. When it does sort, however, it generates a completely different grouping ie I3 and J3 are paired with different golfers. Any Suggestions???? My Copy to: Range is I37

[Thupp]

Mick,

I just thought of another solution. I really do not need to do a random pick. I just need a macro to shuffle the 4 names in the 4 columns in such a way to keep the players in I(n) and J(n) together and pair them with players in K(a) and L(a), but not allowing the 4 from the same pick range row to be paired together. I'd like to start with the last row in the Pick Range I3:L15 and reverse order the
K15 and L15 pairing as the first in row 1 K37 and L37 followed by K14 ,L14 in Row 2 @ K38,L38 and so on. Then do a pick from the I and J columns in the Pick Range and place them starting @ I37 and J37, and adjust them so a pick from the same row of the pick range does not occur in the Pairing Range of K37:L49. The other constraint I have is that the entire Pick Range is not always populated, so I need to be able to skip the blank rows which will always be the bottom rows. There will always be a minimum of 3 players in a row of the Pick Range so there needs to be a way for that row to still be used. The three man rows are always sequenced in the last populated rows of the pick range, and the blank player is always in the L column. This is why I want to reverse order K and L as that will always allow the 3 man pairings to go out first. Any Suggestions???????????????? Your help is greatly appreciated.

Tom