Create Sheet to index scores for a club

[ShiftyThor]

Good day All,

I need to extract data from an excel sheet and index them on another sheet to formulate results with handicaps etc. I am struggling on extracting the data:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Division[/TD]
[TD]Stage Number[/TD]
[TD]Name[/TD]
[TD]Hit Factor[/TD]
[/TR]
[TR]
[TD]Classic[/TD]
[TD]1[/TD]
[TD]Ira[/TD]
[TD]2.57[/TD]
[/TR]
[TR]
[TD]Classic[/TD]
[TD]2[/TD]
[TD]Ira[/TD]
[TD]0.97[/TD]
[/TR]
[TR]
[TD]Standard[/TD]
[TD]1[/TD]
[TD]Jason[/TD]
[TD]5.36[/TD]
[/TR]
[TR]
[TD]Standard[/TD]
[TD]1[/TD]
[TD]Troy[/TD]
[TD]5.29[/TD]
[/TR]
[TR]
[TD]Standard[/TD]
[TD]1[/TD]
[TD]Paul[/TD]
[TD]5.18[/TD]
[/TR]
[TR]
[TD]Standard[/TD]
[TD]1[/TD]
[TD]Dion[/TD]
[TD]2.80[/TD]
[/TR]
[TR]
[TD]Standard[/TD]
[TD]2[/TD]
[TD]Dion[/TD]
[TD]5.36[/TD]
[/TR]
[TR]
[TD]Standard[/TD]
[TD]2[/TD]
[TD]Jason[/TD]
[TD]3.67[/TD]
[/TR]
[TR]
[TD]Standard[/TD]
[TD]2[/TD]
[TD]Paul[/TD]
[TD]3.39[/TD]
[/TR]
[TR]
[TD]Standard[/TD]
[TD]2[/TD]
[TD]Troy[/TD]
[TD]1.61[/TD]
[/TR]
</tbody>[/TABLE]



















I require the below output from the information above:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Division[/TD]
[TD]Stage 1[/TD]
[TD]Handicap[/TD]
[TD]Stage 2[/TD]
[TD]Handicap[/TD]
[/TR]
[TR]
[TD]Ira[/TD]
[TD]Classic[/TD]
[TD]2.57[/TD]
[TD](To be inputted)[/TD]
[TD]0.97[/TD]
[TD](To be inputted)[/TD]
[/TR]
[TR]
[TD]Jason[/TD]
[TD]Standard[/TD]
[TD]5.36[/TD]
[TD](To be inputted)[/TD]
[TD]3.67[/TD]
[TD](To be inputted)[/TD]
[/TR]
[TR]
[TD]Troy[/TD]
[TD]Standard[/TD]
[TD]5.29[/TD]
[TD](To be inputted)[/TD]
[TD]1.61[/TD]
[TD](To be inputted)[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Standard[/TD]
[TD]5.18[/TD]
[TD](To be inputted)[/TD]
[TD]3.39[/TD]
[TD](To be inputted)[/TD]
[/TR]
[TR]
[TD]Dion[/TD]
[TD]Standard[/TD]
[TD]2.8[/TD]
[TD](To be inputted)[/TD]
[TD]5.36[/TD]
[TD](To be inputted)[/TD]
[/TR]
</tbody>[/TABLE]











Basically this is an extract from a much larger database, we would require the persons names, and stage scores on one row taken from another sheet, and then work out handicaps at the end of the row. I am struggling with the extraction (other than a very long string with if's and else's. I am hoping there is a way with indexing and or matching. After the above is achieved, we can work on the rest of the formulae which is straight forward, and we can add more stages once we see if a formula is made in order for us to carry on.

Thanks in advance.

 

[theBardd]

This should do it

Public Sub Reformat()
Dim ws As Worksheet
Dim lastrow As Long
Dim nextrow As Long
Dim targetrow As Long
Dim i As Long
    Application.ScreenUpdating = False
    With ActiveSheet
    
        On Error Resume Next
        Application.DisplayAlerts = False
        Worksheets("Output").Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        
        Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
        ws.Name = "Output"
        ws.Range("A1:F1").Value = Array("Name", "Division", "Stage 1", "Handicap", "Stage 2", "Handicap")
        
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To lastrow
        
            targetrow = FindMatch(.Cells(i, "C").Value, ws.Columns(1))
            If targetrow = 0 Then targetrow = Application.CountA(ws.Columns(1)) + 1
            
            ws.Cells(targetrow, "A").Value = .Cells(i, "C").Value
            ws.Cells(targetrow, "B").Value = .Cells(i, "A").Value
            If .Cells(i, "B").Value = 1 Then
            
                ws.Cells(targetrow, "C").Value = .Cells(i, "D").Value
            Else
            
                ws.Cells(targetrow, "E").Value = .Cells(i, "D").Value
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub
Private Function FindMatch(ByRef lookup As String, lookup_range As Range) As Long
    On Error Resume Next
    FindMatch = Application.Match(lookup, lookup_range, 0)
    On Error GoTo 0
End Function