# Best Solution: Database, Pivot Table, or Neither

#### kilobravo

##### New Member
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

### 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?

B.D...sorry, my FTP error, file should be usable now and thanks for taking up the cause.

Thanks... This helps. What version of excel do you use?

Bd: 2003 sp3

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

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

'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

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

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.

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"

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.

Replies
18
Views
2K
Replies
0
Views
460
Replies
0
Views
365
Replies
0
Views
232
Replies
5
Views
442

1,206,819
Messages
6,075,045
Members
446,115
Latest member
orlamag

### 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.

### Which adblocker are you using?

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

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