Thanks:  0
Likes:  0

# Thread: Combining Two Lists Into One Master List

1. ## 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.

2. ## 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)

 A B 1 Boy1 1 2 Boy2 1 3 Boy3 1 4 Boy4 3 5 Boy5 3

 Cell Formula 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.

3. ## 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. ## 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?

5. ## Re: Combining Two Lists Into One Master List

Originally Posted by Jerry Sullivan
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. ## 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

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
.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, _
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```

7. ## Re: Combining Two Lists Into One Master List

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

8. ## 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. ## 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.

10. ## Re: Combining Two Lists Into One Master List

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

## User Tag List

#### Posting Permissions

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