Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 38

Thread: Combining Two Lists Into One Master List

  1. #1
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Combining Two Lists Into One Master List

    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.

  2. #2
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,756
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Combining Two Lists Into One Master List

    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)

     AB
    1Boy11
    2Boy21
    3Boy31
    4Boy43
    5Boy53

    Spreadsheet Formulas
    CellFormula
    B1=2*ROUNDUP(ROW(A1)/3,0)-1


    Excel tables to the web >> Excel Jeanie HTML 4


    An INDIRECT formula could similarly "pull" the names from the two sheets, but that seems unnecessary if this is a one-timer.
    Using Excel 2016

  3. #3
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combining Two Lists Into One Master List

    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.

  4. #4
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,756
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Combining Two Lists Into One Master List

    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?
    Using Excel 2016

  5. #5
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combining Two Lists Into One Master List

    Quote Originally Posted by Jerry Sullivan View Post
    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.

  6. #6
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,756
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Combining Two Lists Into One Master List

    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
    Using Excel 2016

  7. #7
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combining Two Lists Into One Master List

    Thanks Jerry, I'll be testing this over the weekend.

    Appreciate your time and effort.

  8. #8
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combining Two Lists Into One Master List

    Jerry,

    When I run the code, I get the 400 error message.

    btw, I'm on 2003............

  9. #9
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,756
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Combining Two Lists Into One Master List

    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.
    Using Excel 2016

  10. #10
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combining Two Lists Into One Master List

    Quote Originally Posted by Jerry Sullivan View Post
    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!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •