Combining Two Lists Into One Master List

[Brian from Maui]

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.

 

[Jerry Sullivan]

I'm happy to hear that Brian. (sun glasses for Maui)

 

[Brian from Maui]

Kerry,

I should have anticiapted my freinds next request. I've tried to alter the code you gave with no sucess. The paramters are........

from one master list, assign tee times in groups of three and if there are remiander to do foursomes.

If there is a request for foursome I think I can change that part.

A wish list is assigning tee times on the first and tenth tees with times.

Is this doable/ Ive been tinkering with the code given bt get stck waaaay to ofter.

Mahalo!

 

[Jerry Sullivan]

Hi Brian, Could you post an example of the starting list and desired results?

 

[Brian from Maui]

Aloha Jerry,

For the first part,

It's a straightforward named list housing names in coulmn A and school in column B. The tee times are to be assigned in column C. Again threesomes and foursomes whenver there's a remainder. Tried to alter the code you gave me, but that didn't work out. I was debugging almost every line.

The other request would be assigned tee times on both the 1st and 10th hole. Again named range for 1st tee and 10th tee.

Thanks

 

[Jerry Sullivan]

Ok, the first part is clear. I'm not following what happens differently for the second part. Let's say there's a list Tee1List and another Tee10List. Is the output two lists (on sheets "Tee1" and "Tee10"), or one list that has another column for 1st tee/ 10th tee, or something else?

 

[Brian from Maui]

In some high school tournaments, players go off on the 1st and 10th tees with identical times. I suppose the easiest would be have two named list one 1st tee and the other 10th tee, so the outcome would be like the first request above only with an added column denoting what tee they start from.

As an example

Name Team Tee number Time time

Mahalo!

Off to practice. Glad the season is almost over............getting too old for this.

 

[Jerry Sullivan]

Aloha Brian,

Add these two procedures to the same code module as the previous code.
The code assumes you have already created two named ranges: Tee1List and Tee10List that include the players' names and teams.

Sub MakePlayerPairings2StartTees()
'--builds paring list from two list of players
'  starting at different tees.

   Dim vTee1Data As Variant, vTee10Data As Variant

   '--read player data from two named ranges
   '  this example: Name, Team
   vTee1Data = Range("Tee1List").Value
   vTee10Data = Range("Tee10List").Value
  
   '--add a column to arrays with starting tees
   vTee1Data = vAddStartTees(vPlayers:=vTee1Data, _
      lStartTee:=1)
   vTee10Data = vAddStartTees(vPlayers:=vTee10Data, _
      lStartTee:=10)
   
   '--add a column to arrays with group numbers of pairings
   vTee1Data = vAssignGroups(vPlayers:=vTee1Data, _
      lFirstGroup:=1, lIncrementsGroupsBy:=1)
   vTee10Data = vAssignGroups(vPlayers:=vTee10Data, _
      lFirstGroup:=1, lIncrementsGroupsBy:=1)

   '--write results to new worksheet
   With Worksheets.Add
      .Range("A2").Resize(UBound(vTee1Data, 1), _
         UBound(vTee1Data, 2)).Value = vTee1Data
      .Range("A2").Offset(UBound(vTee1Data, 1)) _
         .Resize(UBound(vTee10Data, 1), _
         UBound(vTee10Data, 2)).Value = vTee10Data
      '--sort by group nbr then start tee
      .Range("A1:D1") = Array("Name", "Team", "Tee Number", "Tee Time")
      With .Range("A1").CurrentRegion
          .Sort Key1:=.Range("D1"), Order1:=xlAscending, _
            Key2:=.Range("C1"), Order2:=xlAscending, _
            Header:=xlYes, Orientation:=xlTopToBottom
      End With
      
      '--replace group numbers with tee times
      AssignTeeTimes rGroupNumbers:=.Range("D2:D" & _
         .Cells(.Rows.Count, "D").End(xlUp).Row), _
         dtStart:=TimeValue("08:30"), _
         dtIncrement:=TimeValue("00:08")
      .UsedRange.EntireColumn.AutoFit
   End With

End Sub

Private Function vAddStartTees(ByVal vPlayers As Variant, _
   ByVal lStartTee As Long) As Variant
'---assumes input vPlayers is a 2D array
'   Returns array that has added column with start tee number
   
   Dim lCountOfPlayers As Long
   Dim lGroup As Long, lNdx As Long, lStartTeeCol As Long
   
   lStartTeeCol = UBound(vPlayers, 2) + 1
   lCountOfPlayers = UBound(vPlayers, 1)
   
   '--adds a field for Start Tee numbers
   ReDim Preserve vPlayers(1 To lCountOfPlayers, 1 To lStartTeeCol)
    
   For lNdx = 1 To lCountOfPlayers
      '--assign start tee
      vPlayers(lNdx, lStartTeeCol) = lStartTee
   Next lNdx
   vAddStartTees = vPlayers
End Function

 

[Brian from Maui]

Aloha Jerry,

soryy being dodging two tsunami alerts, prayers for the Chiliean people.

When I run the code, the output is a single column of names, another column with Team denoting 1 or 10 and a column named team number 111111222222......I'm assuming to assign tee times from this coulmn. but after two tee times, it stops......

MAHALO!

What should happen, or what I'd like is separate columns for the 1st and 10th tee with corresponding tee times in each.

Thanks for your time and effort.

 

[Jerry Sullivan]

Brian, Do your defined names reference 2 columns each Name and Team? The result you describe could happen if the defined named reference a range that is one column wide (just the player names with no teams).

 

[Brian from Maui]

OK, my named range consist of just one column. I'll try two and see what happens.

Thanks