Option Explicit
Function create()

'set up output as a blank string each
Dim output As String
output = ""


'create list of inderviduals

Dim i As Integer
i = 1

Do While Sheets("Entry_Ind").Cells(7 + i, "A").Value <> 0
    output = output & "competitors[" & i & "]=new Array("
    output = output & "0," ' set firstelement as 0 as will become the server reference to this member
    output = output & "'" & Sheets("Entry_Ind").Cells(7 + i, "A").Value& & "'," 'set surname
    output = output & "'" & Sheets("Entry_Ind").Cells(7 + i, "B").Value& & "'," 'set first name
    output = output & "'" & Sheets("Entry_Ind").Cells(7 + i, "C").Value& & "'," 'sets RLSS Number
    output = output & "'" & Sheets("Entry_Ind").Cells(7 + i, "D").Value& & "'," 'sets type
    output = output & "'" & Sheets("Entry_Ind").Cells(7 + i, "E").Value& & "');" & vbLf 'sets DOB
    

i = i + 1

Loop
Cells(1, "A") = output


'create list of solo races


output = ""
i = 1 'track compeittor to enter
Dim k As Integer 'to track the array element ot be updatted
k = 1


Do While Sheets("Entry_Ind").Cells(7 + i, "A").Value <> 0
    Dim j As Integer 'tracks which event to enter
    j = 1
    Do While Sheets("Entry_Ind").Cells(3, j + 7).Value <> ""
        If (Sheets("Entry_Ind").Cells(i + 7, j + 7).Value <> "" And Sheets("processing_ind").Cells(i + 6, j + 5).Value > 0 And j <> 6) Then 'igoresif they were eligible to enter
            output = output & "solos[" & k & "]=new Array("
            output = output & i & "," 'set the competittor involved
            output = output & Sheets("setUP").Cells(11 + j, "A").Value & "," 'set the event involved
            output = output & "'" & ageCode(Sheets("processing_ind").Cells(i + 6, "E").Value + Sheets("processing_ind").Cells(i + 6, j + 5).Value - 1, Sheets("Entry_Ind").Cells(i + 7, "D").Value) & "'," 'puts in the sex and age code needed
            output = output & Sheets("Entry_Ind").Cells(i + 7, j + 7).Value * 3600 * 24 & ");" & vbLf 'adds time
            k = k + 1
        End If
        j = j + 1
    Loop
    i = i + 1
Loop


Cells(1, "B") = output

'ropes output
output = ""
i = 1 'track compeittor to enter
k = 1


Do While Sheets("Entry_Ind").Cells(7 + i, "A").Value <> 0
    j = 1
    Do While Sheets("Entry_Ind").Cells(3, j + 7).Value <> ""
        If (Sheets("Entry_Ind").Cells(i + 7, j + 7).Value <> "" And Sheets("processing_ind").Cells(i + 6, j + 5).Value > 0 And (j = 1)) Then 'igoresif they were eligible to enter
            output = output & "ropes[" & k & "]=new Array("
            output = output & i & "," 'set the competittor involved
            output = output & i & "," 'set the competittor involved
            output = output & Sheets("setUP").Cells(11 + j, "A").Value & "," 'set the event involved
            output = output & "'" & ageCode(Sheets("processing_ind").Cells(i + 6, "E").Value + Sheets("processing_ind").Cells(i + 6, j + 5).Value - 1, Sheets("Entry_Ind").Cells(i + 7, "D").Value) & "'," 'puts in the sex and age code needed
            output = output & Sheets("Entry_Ind").Cells(i + 7, j + 7).Value * 3600 * 24 & ");" & vbLf 'adds time
            k = k + 1
        End If
        j = j + 1
    Loop
    i = i + 1
Loop


Cells(1, "C") = output


'Teams array output
output = ""
i = 1 'track compeittor to enter
k = 1

Do While Sheets("Entry_Team").Cells(3 + i, "A").Value <> 0
    j = 1
    Do While Sheets("Entry_Team").Cells(3, j + 6).Value <> ""
        If (Sheets("Entry_Team").Cells(i + 3, j + 6).Value <> "" And Sheets("processing_tem").Cells(i + 2, j + 5).Value > 0) Then  'igoresif they were eligible to enter
            output = output & "teams[" & k & "]=new Array("
            output = output & "'" & Sheets("Entry_Team").Cells(i + 3, "T").Value & "'," ' add the team Name
            output = output & selectSwimmerSurname(Sheets("Entry_Team").Cells(i + 3, "A").Value, Sheets("Entry_Team").Cells(i + 3, "E").Value, Sheets("processing_tem").Cells(i + 2, "E").Value) & "," 'set the competittor involved
            output = output & selectSwimmerSurname(Sheets("Entry_Team").Cells(i + 3, "B").Value, Sheets("Entry_Team").Cells(i + 3, "E").Value, Sheets("processing_tem").Cells(i + 2, "E").Value) & "," 'set the competittor involved
            output = output & selectSwimmerSurname(Sheets("Entry_Team").Cells(i + 3, "C").Value, Sheets("Entry_Team").Cells(i + 3, "E").Value, Sheets("processing_tem").Cells(i + 2, "E").Value) & "," 'set the competittor involved
            output = output & selectSwimmerSurname(Sheets("Entry_Team").Cells(i + 3, "D").Value, Sheets("Entry_Team").Cells(i + 3, "E").Value, Sheets("processing_tem").Cells(i + 2, "E").Value) & "," 'set the competittor involved
            output = output & Sheets("setUP").Cells(26 + j, "A").Value & "," 'set the event involved
            output = output & "'" & ageCode(Sheets("processing_tem").Cells(i + 2, "E").Value + Sheets("processing_tem").Cells(i + 2, j + 5).Value - 1, Sheets("Entry_Team").Cells(i + 3, "E").Value) & "'," 'puts in the sex and age code needed
            output = output & Sheets("Entry_Team").Cells(i + 3, j + 6).Value * 3600 * 24 & ");" & vbLf 'adds time
            k = k + 1
        End If
        j = j + 1
    Loop
    i = i + 1
Loop


Cells(1, "D") = output

End Function


'takes the age group and sex and produces the single output code
Function ageCode(i As Integer, j As String) As String
        Dim n As Integer
        If (j = "M") Then
            n = 1
        Else
            If (j = "f") Then
                n = 2
            Else
                n = 3
            End If
        End If
        ageCode = Sheets("setUP").Cells(n + 19, i + 12).Value
End Function



'take surname, sex and age group and find the best match
'names worht the most
'then sex
'then age group
'then younger age

Function selectSwimmerSurname(s As String, sex As String, ag As Integer) As Integer
    Dim i As Integer
    i = 1
    Dim currentBestScore As Integer
    Dim idBestScore As Integer
    idBestScore = 0
    currentBestScore = -1
    Do While Sheets("Entry_Ind").Cells(7 + i, "A").Value <> 0
        Dim score As Integer
        score = 0
        If Sheets("Entry_Ind").Cells(7 + i, "A").Value = s Then score = score + 30
        If Sheets("Entry_Ind").Cells(7 + i, "D").Value = sex Then score = score + 15
        If Sheets("Entry_Ind").Cells(7 + i, "G").Value = ag Then score = score + 2
        If Sheets("processing_ind").Cells(6 + i, "D").Value = ag Then score = score + 2
        If Sheets("processing_ind").Cells(6 + i, "E").Value = ag Then score = score + 2
        If Sheets("processing_ind").Cells(6 + i, "D").Value < ag Then score = score + 1
        If score > currentBestScore Then
            currentBestScore = score
            idBestScore = i
        End If
        i = i + 1
    Loop
    selectSwimmerSurname = idBestScore

End Function