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]

If you add Private Function vAddStartTees (from Post #17) to that module, then it should have all the parts it needs to compile.

 

[Brian from Maui]

Would it be easier for me if I split the two procedures in two diferrent workbooks?

 

[Jerry Sullivan]

I don't think that will make it easier. You just need that one missing piece: Private Function vAddStartTees (from Post #17)

 

[Brian from Maui]

Thnks Jerry,

I'll try and figure it out over the weekend. I do appreciate your time and effort to teach/write me this procedure.

I value your expertise and time spent on this.

MAHALO!

 

[Brian from Maui]

Jerry,

I finally figured it out, thank you very much. Assigning 1st and 10th tees with tee times on separate list is AWESOME.

Caveat to the first procedure with alternating boys and girls. How could I add another column for schools so it would be

name, school, tee time?

I recorded a macro to insert a column and do VLOOKUP, and it does the trick.

I tried chaning the array from A1:B1 to A1:C1, but that obviously didn't work.

 

[Jerry Sullivan]

Aloha Brian,

You can replace the old Sub with this Sub in the same code module.

Sub MakePlayerSchoolPairingsList()
'--makes list of pairings alternating boys and girls players.
'  assumes workbook has two named ranges setup: BoysList,GirlsList
'  each named range is two columns: player name; school

   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("C1"), Order1:=xlAscending, _
          Header:=xlYes, Orientation:=xlTopToBottom
      End With
      
      '--replace group numbers with tee times
      AssignTeeTimes rGroupNumbers:=.Range("C2:C" & _
         .Cells(.Rows.Count, "C").End(xlUp).Row), _
         dtStart:=TimeValue("08:30"), _
         dtIncrement:=TimeValue("00:08")
         
      .Range("A1:C1") = Array("Player Name", "School", "Tee Time")
      .UsedRange.EntireColumn.AutoFit
   End With

End Sub

 

[Brian from Maui]

Jerry,

I get a Compile Error, Sub or function not defined

errors at vAssignGroups

 

[Brian from Maui]

Jerry,

I figured it out........thank you very much. Appreciate all your time