Home/ Questions/Q 5781
Next
In Process
Computing Staff
  • 0
Asked: 2021-11-16T14:00:00+00:00 In:

Finding Matching Pairs In Excel Using Vba

  • 0

I have 24 players playing billiards using 2-person teams. Thus 4 people are playing each other at any given time. There are 10 weeks of playing. I want to create a playing schedule such that duplicates are minimized over the 10 weeks. There are 3 pool tables so there is an early and late session to accommodate all players. (Added bonus: can all players play equally early and late sessions?)
Team AB playing team CD is the same as Team BA vs. Team DC.
I’m new to VBA but know enough to get by so give me your best shot.

Share

    1 Answer

    1. I just re-read my post and feel that I should clarify something.

      I said:

      The code creates a randomly sorted list of players for each week. e.g.

B, G, D, S, E, A, F, H, etc.

It then pairs those players based on that random order e.g.

BG, DS, EA, FH

      In reality, the code only creates the randomly sorted lists of players for each week. It places those lists in C1:L24. It is the formulas in M1:V23 that actually create the pairs. Then the COUNTIF formulas in M25:V47 check for duplicates.

      The COUNTIF formula in L26 then determines if there are any duplicates by checking to see if there are any values greater than 1 in the M25:V47 table.

      It is the formula in L26 that the code monitors to determine if there needs to be a re-randomization of the current week. If anything other than 0 is in L26 after the current week’s pairings are checked, then there must be at least one duplicate and the week must be rerun.

      As I mentioned in my previous post, this process is not as clean as it could be. The worksheet is also not as pretty as it could be. I managed to come up with something that works even if it is a bit clunky. At that point I quit and posted what I had. Once you understand how it works, you should be able to make it look nice. If not, just come on back.

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      message edited by DerbyDad03

      • 0