Best Solution: Database, Pivot Table, or Neither

kilobravo

New Member
Joined
Jun 25, 2012
Messages
18
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

b.downey

Active Member
Joined
Oct 16, 2011
Messages
484
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?
 

kilobravo

New Member
Joined
Jun 25, 2012
Messages
18
B.D...sorry, my FTP error, file should be usable now and thanks for taking up the cause. :)
 

b.downey

Active Member
Joined
Oct 16, 2011
Messages
484
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"

Code:
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")
    WsRawData.Activate
    WsRawData.Range("A1").Select
    
    Set WsDest = Wb.Worksheets("Results")
    WsDest.Cells.Clear
    
    WsRawData.Activate
    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
        
        'Header
        WsDest.Cells(Lastrow + 2, "A") = "Stage: " & intSt
        
        'Data
        WsRawData.AutoFilter.Range.Copy Destination:=WsDest.Range("A" & Lastrow + 3)
    Next intSt
    
    WsRawData.AutoFilterMode = False
    WsDest.Activate
    
    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
 

kilobravo

New Member
Joined
Jun 25, 2012
Messages
18

ADVERTISEMENT

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")
 

kilobravo

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

b.downey

Active Member
Joined
Oct 16, 2011
Messages
484
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"
 

kilobravo

New Member
Joined
Jun 25, 2012
Messages
18
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 this..be back in a minute or two.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,089
Messages
5,599,667
Members
414,326
Latest member
Aerith

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 MrExcel.com.
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 "mrexcel.com".
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
Top