Combining Two Lists Into One Master List

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
I'm trying to help a friend automate assigning tee times for a high school league invovling boys and girls.

I have a Boys list and a Girls list. The lists are unequal in size, with the boys having more players. I need to combine both lists into a master list to assign tee times. Each has a worksheet named Boys/Girls.

The criteria is to alternate, in groups of three, from each list, until all names are listed in the master list.

Like

Boy1
Boy2
Boy3
Girl1
Girl2
Girl3
Boy4
Boy5
Boy6
Girl4
Girl5
Girl6

I suppose, if worse comes to worst, I could manually do

=BoysList!A1

etc.

Any advice welcome.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi Brian, Are you wanting a VBA solution or just a quick and easy process to do a one-time task?

One option would to add a Group # Column to each sheet >
Copy-Paste Values to put both lists on one sheet >
Sort by Group #

The Boys formula could be:
=2*ROUNDUP(ROW(A1)/3,0)-1

The Girls formula could be (B1 of Girls sheet and copy down):
=2*ROUNDUP(ROW(A1)/3,0)
Excel Workbook
AB
1Boy11
2Boy21
3Boy31
4Boy43
5Boy53
Sheet



An INDIRECT formula could similarly "pull" the names from the two sheets, but that seems unnecessary if this is a one-timer.
 
Upvote 0
Aloha Jerry

Sorry for the delay in responding.............been out golfing.

It would be a 90 hole tournament, so repairing after every 18.

I'll try this method tomorrow and get back to you. Thanks!

btw, I would be open to a VBA solution.
 
Upvote 0
Aloha Brian,

I'd be glad to suggest some VBA code. Aside from merging the two lists by taking 3 players at time from each list, is there some other process to be automated for each repairing?
 
Upvote 0
Aloha Brian,

I'd be glad to suggest some VBA code. Aside from merging the two lists by taking 3 players at time from each list, is there some other process to be automated for each repairing?

Aloha Jerry,

I'll create named ranges for both boys and girls.

If either list isn't divisible by three with no remainders, it will pair foursomes.

Can't mix boys and girls.

Maybe assign tee times from a list.

A big MAHALO from the Maui Interscholastic League.
 
Upvote 0
Brian, Aloha to you and the good people of the Maui Interscholastic League.

Handling the addition of foursomes when needed was an interesting wrinkle.

Here's some code you can try.

Code:
Sub MakePlayerPairingsList()
   Dim vBoysNames As Variant, vGirlsNames As Variant
   Dim vAllNames As Variant, vResults As Variant

   '--read names from two named ranges (add validation if needed)
   vBoysNames = Range("BoysList").Value
   vGirlsNames = Range("GirlsList").Value
  
   '--add a column to arrays with group numbers of pairings
   vBoysNames = vAssignGroups(vPlayers:=vBoysNames, _
      lFirstGroup:=1, lIncrementsGroupsBy:=2)
   vGirlsNames = vAssignGroups(vPlayers:=vGirlsNames, _
      lFirstGroup:=2, lIncrementsGroupsBy:=2)

   '--write results to new worksheet
   With Worksheets.Add
      .Range("A2").Resize(UBound(vBoysNames, 1), _
         UBound(vBoysNames, 2)).Value = vBoysNames
      .Range("A2").Offset(UBound(vBoysNames, 1)) _
         .Resize(UBound(vGirlsNames, 1), _
         UBound(vGirlsNames, 2)).Value = vGirlsNames
      With .Range("A1").CurrentRegion
          .Sort Key1:=.Range("B1"), Order1:=xlAscending, _
          Header:=xlYes, Orientation:=xlTopToBottom
      End With
      
      '--replace group numbers with tee times
      AssignTeeTimes rGroupNumbers:=.Range("B2:B" & _
         .Cells(.Rows.Count, "B").End(xlUp).Row), _
         dtStart:=TimeValue("08:30"), _
         dtIncrement:=TimeValue("00:08")
         
      .Range("A1:B1") = Array("Player Name", "Tee Time")
      .UsedRange.EntireColumn.AutoFit
   End With

End Sub


Private Sub AssignTeeTimes(ByVal rGroupNumbers As Range, _
   ByVal dtStart As Date, ByVal dtIncrement As Date)
            
   Dim dtTeeTime As Date
   Dim sLastGroup As String
   Dim rGroup As Range
   
   '--initial values
   dtTeeTime = dtStart
   sLastGroup = rGroupNumbers(1)
   
   For Each rGroup In rGroupNumbers
      If rGroup.Value <> sLastGroup Then
         '--next group
         sLastGroup = rGroup.Value
         dtTeeTime = dtTeeTime + dtIncrement
      End If
      rGroup.Value = dtTeeTime
   Next rGroup
   rGroupNumbers.NumberFormat = "h:mm AM/PM"
End Sub

            
Private Function vAssignGroups(ByVal vPlayers As Variant, _
   ByVal lFirstGroup As Long, ByVal lIncrementsGroupsBy As Long) As Variant
'---assumes input vPlayers is a 2D array and first column has players names.
'   Returns array that has added column with group numbers of pairings.
'   Assigns 3 players per group until need to switch to
'   4 players per groups to have remainder of 0.
'   Group numbers start at lFirstGroup and increase by lIncrementsGroupsBy

   Dim lCountOfPlayers As Long, lLastInThreesome As Long
   Dim lSpotsInGroup As Long, lSpotsOpen As Long
   Dim lGroup As Long, lNdx As Long, lGroupCol As Long
   
   lGroupCol = UBound(vPlayers, 2) + 1
   lCountOfPlayers = UBound(vPlayers, 1)
   
   '--adds a field for Group Assignments
   ReDim Preserve vPlayers(1 To lCountOfPlayers, 1 To lGroupCol)
 
   '--calculate when groups must switch to foursomes
   lLastInThreesome = lCountOfPlayers - 4 * (lCountOfPlayers Mod 3)
      
   '--start with 3 per group except case of 4 or 8 players
   Select Case lCountOfPlayers
      Case 4, 8: lSpotsInGroup = 4
      Case Else: lSpotsInGroup = 3
   End Select
   lSpotsOpen = lSpotsInGroup
   lGroup = lFirstGroup
   
   For lNdx = 1 To lCountOfPlayers
      If lSpotsOpen = 0 Then
         '--start new group with all spots open
         lGroup = lGroup + lIncrementsGroupsBy
         lSpotsOpen = lSpotsInGroup
      End If
      
      '--assign group to player
      vPlayers(lNdx, lGroupCol) = lGroup
      lSpotsOpen = lSpotsOpen - 1
      
      If lNdx = lLastInThreesome Then
         lSpotsInGroup = 4
      End If
      
   Next lNdx
   vAssignGroups = vPlayers
End Function
 
Upvote 0
Brian, I don't think there's anything in the code that would be incompatible with 2003. More likely something about your workbook setup is different than my assumptions.

The code assumes you have created named ranges "BoysList" and "GirlsList". These should be just one column ranges with the players names.

When you get the error message, does it give you the option to debug and see on which line it occurred?

If not, try stepping through the code one line at a time using the F8 key to see if you can narrow down what is causing the error.
 
Upvote 0
Brian, I don't think there's anything in the code that would be incompatible with 2003. More likely something about your workbook setup is different than my assumptions.

The code assumes you have created named ranges "BoysList" and "GirlsList". These should be just one column ranges with the players names.

When you get the error message, does it give you the option to debug and see on which line it occurred?

If not, try stepping through the code one line at a time using the F8 key to see if you can narrow down what is causing the error.

My bad Jerry, I figured it out. I inserted the code in a module and everything works AWESOME!
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top