Best Solution: Database, Pivot Table, or Neither


New Member
Jun 25, 2012
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.


Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I am able to access the Scores link and see the "result" you are looking to obtain,, but I cannot connect to the "Test.XLS" sheet that you posted. I am fairly certian this task can be accomplished using VBA, but I would really need to see the "Test.XLS" for to make that determination. Can you correct the link?
Upvote 0
This is work in progress.... It assumes that the Raw Data in in a Sheet called "RawData" and also assumes that there is a second sheet in the workbook called "Results".

The Code will create the data for the 6 "Stages"

Option Explicit
Dim Wb As Workbook
Dim WsRawData As Worksheet
Sub Process()
    Dim WsDest As Worksheet
    Dim intSt As Integer
    Dim Lastrow As Long
    Set Wb = ThisWorkbook
    Set WsRawData = Wb.Worksheets("RawData")
    Set WsDest = Wb.Worksheets("Results")
    WsRawData.AutoFilterMode = False
    WsRawData.UsedRange.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'
        Lastrow = WsDest.Cells(WsDest.Rows.Count, "A").End(xlUp).Row
        WsDest.Cells(Lastrow + 2, "A") = "Stage: " & intSt
        WsRawData.AutoFilter.Range.Copy Destination:=WsDest.Range("A" & Lastrow + 3)
    Next intSt
    WsRawData.AutoFilterMode = False
    MsgBox "Complete", vbInformation
End Sub

The Code simple needs to be places in a VBA module in the Workbook. Once it is there, it will show up as a Macro for anyone to execute.

If this meets you needs, we can get the formatting done as you describe and also the other sections of the report.
Finals by Division
Finals by Score
Finals by Pen
Upvote 0
DB: Started with a fresh temp.xls, added a module and pasted in the code. When I run it I get:

Run-time error '9':
Subscript out of range

and the Debugger highlights this line:

Set WsRawData = Wb.Worksheets("RawData")
Upvote 0
I failed to mention that when I FTP'd the new temp.xls, I decided to give you a full match worth of data and did another FTP with the full file. So, if you're using the previous temp.xls with less than 201 rows of data, that might be the problem, I don't know.
Upvote 0
The error that was generated was beacuse you don't have a tab (worksheet) called "RawData". The Data is probally in "Sheet1". If it is always going to be in "Sheet1", I can change the code to look for Sheet1 rather than "RawData"
Upvote 0
I thought about creating the worksheet and then trying it but decided I wasn't the expert. Anyway, let me try that now and see how it goes...I REALLY appreciate back in a minute or two.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back