Best Solution: Database, Pivot Table, or Neither

[kilobravo]

First post for me..long-time Excel user but only semi-literate with the use of Lists or Pivot Tables. My question is what would be the best direction to go for my needs. To wit, I help run a competitive rifle/handgun shooting club and as the "Scores Guy," I would like to improve and automate the manual method I've been using.

To give an idea of the task at hand, we typically have 25 shooters each of which will shoot five stages (ST) which generates 125 rows of data. A shot timer records the raw time (TIME) and there are multiple types of penalties all of which can add additional time to the score. A lower score, i.e., time is better. The final score is the sum of the raw time plus penalties and the penalties field (PEN) is calculated by multiplying penalty factor assignments to each type of penalty times the number of occurrences.

This is a link to a portion of a sample data file... (temp.xls)

and here is a link to an example of the html file we use to post the scores. (scores link)

Right now, raw data is entered manually from random scoresheets in a stack, one line at a time, and that data is then replicated via multiple formulas in multiple sheets of the same workbook where calculations and totalling is done followed by additional sheets where sorting is done and finally, an additional sheet for the Web output file using the gray bars you see in the html link.

So, my question is whether using a List or Pivot Table would be better or, whether neither is appropriate. I'm not at all VBA savvy but I've done a good bit of dBASE and other programming in the distant past so I'm at least teachable. Ultimately, I'd love to have a turn key system that a non-techie could use to do our scores but I realize that's a pie in the sky goal. However, I'd be happy with some guidance on how to do this in an efficient manner even if it means I have to be the operator during the process, i.e., dialog boxes for entry, VBA buttons for manipulating the data and producing the HTML output.

Any guidance would be greatly appreciated.

KB

 

[kilobravo]

Renamed the data sheet to RawData and added the Results sheet...

WOW!!!! PERFECT!

Now I almost feel guilty asking for further help, DB but, since you seem to be willing...

1) Each of those first individual stage listings need to be sorted on DIV and SCORE

2) Then, as you mentioned, the last three listings are as depicted:

Finals by Division (sorted again on DIV then SCORE)
Finals by Score (sorted by SCORE)
Finals by Pen (sorted by PEN then SCORE)

3) I need a column inserted for ranking before column A and while I can happily enter the rankings manually, if it can be automated, even better. As you'll see, all the lists sorted by DIV then SCORE have place rankings by DIV while the last two lists on the web page are ranked top to bottom numerically with no separation of DIV.

And finally, a question...will this routine work regardless of the number of stages or competitors? Sometimes we have 5 stages, sometimes 7 or 8 and at our big annual matches, we might have 150 competitors and 7 or 8 stages.

Gosh..I don't begin to know how to thank you for this but I'd sure like to send you some $$$ for your help and the time you've spent.

 

[kilobravo]

Forgot to mention that I do not need the ST column in the lists, it is only in the data for the filtering. As long as the ST number is indicated above each list, that's enough.

 

[kilobravo]

Well, I answered my own question, the routine only works for 5 stages but changing the For loop from 1-5 to 1-8 did the trick.

So let me know if you're still interested in fine tuning this or not, BD, and if not, I'll start plugging away on it myself.

Thanks again..

KB

 

[b.downey]

One step closer... All of the Basic Data is provided. 5 Stages and the Three additional Summary Sheets. I have also left room for the ranking. Just a little more to acomplish

Option Explicit
Dim Wb As Workbook
Dim WsRawData As Worksheet

Sub Process()
    Dim WsDest As Worksheet
    Dim intSt As Integer
    Dim SrcLastRow As Long
    Dim DestLastRow As Long
    
    Set Wb = ThisWorkbook
    Set WsRawData = Wb.Worksheets("RawData")
    WsRawData.Activate
    WsRawData.Range("A1").Select
    
    'Turn the filters off
    If WsRawData.FilterMode Then
        WsRawData.Range("A1").AutoFilter
    End If
    
    Set WsDest = Wb.Worksheets("Results")
    WsDest.Cells.Clear
    
    WsRawData.Activate
    WsRawData.AutoFilterMode = False
    WsRawData.UsedRange.Activate
    
    SrcLastRow = WsRawData.Cells(WsDest.Rows.Count, "A").End(xlUp).Row
    WsRawData.Range("A1:L" & SrcLastRow).Sort Key1:=WsRawData.Range("B1"), Key2:=WsRawData.Range("L1"), Header:=xlYes
    
    For intSt = 1 To 5
        Selection.AutoFilter Field:=3, Criteria1:=intSt 'Field 3 is the 'Stage'
        
        DestLastRow = WsDest.Cells(WsDest.Rows.Count, "B").End(xlUp).Row
        WsDest.Cells(DestLastRow + 2, "A") = "Stage: " & intSt
        
        'Data
        WsRawData.AutoFilter.Range.Copy Destination:=WsDest.Range("B" & DestLastRow + 3)
    Next intSt
    
    'Turn the filters off
    If WsRawData.FilterMode Then
        WsRawData.Range("A1").AutoFilter
    End If
    
    '***** Final By Division
    WsRawData.Range("A1:L" & SrcLastRow).Sort Key1:=WsRawData.Range("B1"), Key2:=WsRawData.Range("L1"), Header:=xlYes
    DestLastRow = WsDest.Cells(WsDest.Rows.Count, "B").End(xlUp).Row
    WsDest.Cells(DestLastRow + 2, "A") = "Final By Division"
    WsRawData.Range("A1:L" & SrcLastRow).Copy Destination:=WsDest.Range("B" & DestLastRow + 3)
    
    '***** Final By Score
    WsRawData.Range("A1:L" & SrcLastRow).Sort Key1:=WsRawData.Range("L1"), Header:=xlYes
    DestLastRow = WsDest.Cells(WsDest.Rows.Count, "B").End(xlUp).Row
    WsDest.Cells(DestLastRow + 2, "A") = "Final By Score"
    WsRawData.Range("A1:L" & SrcLastRow).Copy Destination:=WsDest.Range("B" & DestLastRow + 3)
    
    '***** Final By Pen
    WsRawData.Range("A1:L" & SrcLastRow).Sort Key1:=WsRawData.Range("K1"), Header:=xlYes
    DestLastRow = WsDest.Cells(WsDest.Rows.Count, "B").End(xlUp).Row
    WsDest.Cells(DestLastRow + 2, "A") = "Final By Penalties"
    WsRawData.Range("A1:L" & SrcLastRow).Copy Destination:=WsDest.Range("B" & DestLastRow + 3)
    
    WsDest.Activate
    WsDest.Range("A1").Activate
    
    MsgBox "Complete", vbInformation
End Sub

 

[kilobravo]

Absolutely AWESOME, BD but there is one thing..all three of the listings at the bottom are totals for all the stages by shooter name. That is, one row in TOTALS BY DIVISION are the totals of all the raw times, penalties, and scores from the individual stages above.

 

[b.downey]

Ok... Now I understand... Let me take a look at that aspect of the requirement

 

[b.downey]

Ok... You need to add one more SpreadSheet (Tab) called Summary. This sheet will contain the needed information for the 3 Summary reports that you need. The information on the Summary Sheet is not yet populate on the "Reports" sheet, but i'll see if I can get that done tomorrow:

Here is the revised code:

Option Explicit
Type typeRec
    Name As String
    Div As String
    Cnts(9) As Single
End Type
Dim Wb As Workbook
Dim WsRawData As Worksheet

Sub Process()
    Dim WsDest As Worksheet
    Dim intSt As Integer
    Dim SrcLastRow As Long
    Dim DestLastRow As Long
    
    Set Wb = ThisWorkbook
    Set WsRawData = Wb.Worksheets("RawData")
    WsRawData.Activate
    WsRawData.Range("A1").Select
    
    'Turn the filters off
    If WsRawData.FilterMode Then
        WsRawData.Range("A1").AutoFilter
    End If
    
    Set WsDest = Wb.Worksheets("Results")
    WsDest.Cells.Clear
    
    WsRawData.Activate
    WsRawData.AutoFilterMode = False
    WsRawData.UsedRange.Activate
    
    SrcLastRow = WsRawData.Cells(WsDest.Rows.Count, "A").End(xlUp).Row
    WsRawData.Range("A1:L" & SrcLastRow).Sort Key1:=WsRawData.Range("B1"), Key2:=WsRawData.Range("L1"), Header:=xlYes
    
    For intSt = 1 To 5
        Selection.AutoFilter Field:=3, Criteria1:=intSt 'Field 3 is the 'Stage'
        
        DestLastRow = WsDest.Cells(WsDest.Rows.Count, "B").End(xlUp).Row
        WsDest.Cells(DestLastRow + 2, "A") = "Stage: " & intSt
        
        'Data
        WsRawData.AutoFilter.Range.Copy Destination:=WsDest.Range("B" & DestLastRow + 3)
    Next intSt
    
    'Turn the filters off
    If WsRawData.FilterMode Then
        WsRawData.Range("A1").AutoFilter
    End If
    
    
    '********************************
    ' Summary Tables
    '********************************
    Dim RowNo As Long
    Dim Idx As Long
    Dim Rec() As typeRec
    Dim I As Long
    
    ReDim Rec(0)
    For RowNo = 2 To SrcLastRow
        Idx = FindIdx(WsRawData.Cells(RowNo, "A"), Rec)
        Rec(Idx).Name = WsRawData.Cells(RowNo, "A")
        Rec(Idx).Div = WsRawData.Cells(RowNo, "B")
        For I = 1 To 9
            Rec(Idx).Cnts(I) = Rec(Idx).Cnts(I) + Val(WsRawData.Cells(RowNo, I + 3))
        Next I
    Next RowNo
    
    Dim WsSummary As Worksheet
    
    Set WsSummary = Wb.Worksheets("Summary")
    WsSummary.Cells.Clear
    
    For RowNo = 1 To UBound(Rec)
        WsSummary.Cells(RowNo, 1) = Rec(RowNo).Name
        WsSummary.Cells(RowNo, 2) = Rec(RowNo).Div
        For I = 1 To 9
            WsSummary.Cells(RowNo, I + 2) = Rec(RowNo).Cnts(I)
        Next I
    Next RowNo
    WsDest.Activate
    WsDest.Range("A1").Activate
    
    MsgBox "Complete", vbInformation
End Sub
Function FindIdx(Name As String, Rec() As typeRec) As Long
    Dim I As Long
    
    For I = 1 To UBound(Rec)
        If Trim(Name) = Rec(I).Name Then
            FindIdx = I
            Exit Function
        End If
    Next I
    
    ReDim Preserve Rec(I)
    Rec(I).Name = Name
    FindIdx = I
End Function

 

[kilobravo]

You are an absolute genius with this stuff, DB..really and I'm serious about wanting to send some $$ your way for this.

10-4 on populating a Reports sheet tomorrow, I'm standing by and meanwhile, I'm going to run some previous match data through the Process routine tonight for testing purposes.

Thank you, thank you, THANK YOU!

KB

 

[kilobravo]

Another little oddity, BD, I just noticed and failed to mention to you. If there is a "DNF" for any shooter on any stage, any totals for that shooter should simply show "DNF" in the SCORE column with no other totals in the preceding columns. "DNF" = Did Not Finish and they should sort to the bottom of any totals/summaries. For any stages that shooter name did complete, the data is fine in the individual stage listings but none of their data should be totaled in the three summary listings at the bottom. Hope that's clearer than mud.

KB

 

[kilobravo]

I found another oddity that I didn't explain and I'm feeling guilty so if you're weary of all this, just say so. If not, here's the second fly in the ointment. Shooters can shoot more than one gun at a match, most times those guns are in different DIVisions but sometimes, as was the case with the data for the test match, two shooters actually shot two guns in the same DIV. Here are the 2 gun shooters and their DIV's:

Eddie Munster	Limited
Eddie Munster	Limited
HAL 9000	22 Rifle Limited
HAL 9000	22 Rifle Limited
Hector Ramirez	22 Rifle Limited
Hector Ramirez	Pistol Limited
Mike Mansilla	22 Rifle Limited
Mike Mansilla	Pistol Limited
Roy Richter	22 Rifle Limited
Roy Richter	Tactical
Vince Dionio	22 Rifle Open
Vince Dionio	Pistol Open
Yarek Mazurkiewicz	Limited
Yarek Mazurkiewicz	Pistol Limited

<colgroup><col><col></colgroup><tbody>
</tbody>

I don't know if this can be handled in the Summary and Reports routines or not but I will certainly understand if you've had enough.

My apologies,

KB