this will copy the data from the form over to another sheet. you will need to change the cell/ sheet references in the code

Sub AddToDatabase()


Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCell As Range

'cells to copy from Input sheet - some contain formulas
myCopy = "n12,n14,n16,n18,n20,n22,n24,n26"

Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Database")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With

With historyWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
.Cells(nextRow, "B").Value = Application.username
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts)
.ClearContents
Application.Goto .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With

End Sub

I'd like to create a list of players in a given sport and enter statistics for a season, and then be able to add data for other seasons later on. I do this in Excel right now in a rather painful process that involves me importing the data for the latest season into a new spreadsheet, then having my existing spreadsheet with data for the last 5+ years and the new spreadsheet with the current years data open side-by-side. I then insert rows into each spreadsheet as required so the data lines up, and then combine the two spreadsheets. Very cumbersome, and I know there has to be a better way of doing this. Am I better off using a database? I do a fair bit of calculations with the data, which is one of the reasons I've always used Excel.

If I did want to use a database, what is the best free/cheap option for something like this? I have a few other similar projects I might like to do - most aren't going to involve more than a few thousand records, if that makes a difference.

Thank you

Should the location of a table used for vlookup matter?

I've a table of names with values for each.

I also have a table where a name is entered on each row, and the cell beside it does a vloopup to the other table to get the value. What I'm finding is that as I add more names eventually the vlookup gives me an error, but if I move the other table with the data down a row, then the vlookup works.


The code I'm using is:

=IF(B19="","",VLOOKUP(B19,Z$16:AA$36,2,AA$16:AA$36 ))

Try =IF(B19="","",VLOOKUP(B19,$Z$16:$AA$36,2,FALSE))

The range_lookup entry is a logical entry. FALSE for an exact match. And though it won't matter much as long as you are doing a vlookup on only 1 field, it doesn't hurt to use absolute cell references. Just make sure that $Z$16:$AA$36 is the entire array you want to lookup on.

Aside from that, it could be a data type or formatting issue as well. Make sure that the field you are matching across tables is formatted the same way. If you think this might be the case, try using trim() and clean().

If you can explain how your data is structured and how you append it that would be helpful. What does the raw data look like when you pull it? And how many exports do you have?

(Are your rows unique by player name or no?)

If it's only a few thousand rows and a couple files and you want to do analysis, it's probably easiest to just stick with Excel.

But if you want to append a lot of different exports then it might be easier to write a single load script for SQL and just run it every time you have a new file. And then write queries to structure the data how you want for your analysis.

hey, thanks for this great excel thread.. I have a question about making some sort of cockpit, basicly I have 2 worksheets within 1 file. The first is the index or cockpit and second is the raw data sheet. In the raw data sheet are listed employees with orders per date. So employee X has 3 rows on him for a different orders that occured on 3 different days;
jimmy 01-01-2012 50$
sally 01-01-2012 40$
jimmy 02-01-2012 50$
sally 01-01-2012 40$
jimmy 03-01-2012 50$

Is there an easy way to have the employees listed uniquely in a dropdown menu, being able to select them from there and once selected have a range of predetermined fields being filled ? So if I select jimmy in the dropdown it will show total order $, amount of days worked and more based on the info in that row in the raw data sheet. Whats the easiest way to go about this ?

You can do it with drop downs. I use combo boxes for stuff like that.

In another sheet create a table of numbers 1 to x in one col, employee names on the next.

In the format control of the combo box your input range would be the list of employees.

Have the answer link somewhere (I usually just hide it behind the combo box). This will give you a number which you can link back to the names (in the table).

Then vlookup the output of the drop down in the table ofnames and sumif, countif, vlookup (depending on what you want) into your data.

I've probably made that sound a lot more complicated than it is.

Or use a pivot table.

Pivot table is pretty good and easy but was actually trying to learn some new excel moves while creating ;D Ill try your first option, sounds doable..

Here's the website I've normally been using:



So what I've done is used one of those for my first year. I cut it from that site and paste it into Excel. I take out just about everything but the Name, Team, GP, G, A, P. I take the name row and use Text to Columns so I can create separate First Name and Last Name columns (doing a little manual work on the half dozen multi-name entries like James van Riemsdyk). When I add years, I do the same thing with the current year in a separate spreadsheet, sort each by last name/first name, then put the two side-by-side. The new list will have some new names, and will be missing some from the old. So I go through each list and insert blank rows so that in the end, the names common to both lists (which is by far the majority) will be on the same rows in each spreadsheet. Then I cut the data from the newer sheet and paste it in columns to the right of the data in the existing sheet.

As for data manipulation, I calculate points per game, multi-year averages for GP, weighted averages for points per game, and then a more complicated forumla to project points for the upcoming season. This is the part that makes me hesitant to go to a database.

Right now, my spreadsheet has <1000 rows (I drop off some of the lowest producing names), and probably well under 100 columns. Each year, it grows by <100 rows and around 10 columns, so it would take many years to go beyond two or three thousand rows and a couple hundred columns.

Maybe what I really need is just a better way to import and combine the data.

Two other future projects in the spreadsheet vs. database category:

Movie collection - I'd like to put my movies in a spreadsheet (geek alert!), which would just be a simple listing of title, year, director, genre, key actors, etc. No data manipulation required, and this would be a pretty simple list. Since I'm more comfortable with a spreadsheet, that seems the simple solution. But if something else I'm doing needs me to get a database program, maybe that would be better for this.

Movie database - this is something I'm considering doing down the road for a website. It would be a database of award-winning/nominated movies, and I guess the unique/key fields would be the actual award. For example - 1976 best picture Academy Award. I'd want the year and award/nomination to be separate fields, but both would be required together to make the entry unique, if that makes sense. IE there will be multiple 1976 award winners, multiple best picture winners, but only one 1976 best picture winner. However, there would be multiple 1976 best picture nominees, each of which should probably have its own unique record. Not sure what is the best way to organize this. This database wouldn't require any calculations aside from a record count (eg, how many awards/nominations did movie x or actor y get), but I would want to be able to sort/count by a number of different fields. Database or spreadsheet? I suppose the answer might depend on how I use it with the website. If I am keeping the data off site and just publishing the results of queries on the website (eg static listings of different queries like all the best picture winners, who has won the most best actor awards, etc.), will a spreadsheet suffice? I assume that if I want the data to be part of the site and able to be manipulated by site visitors, it would need to be a database?

Sorry to lump all of this in one post; I expect many were thinking tl; dr less than halfway through. But I'm asking all the questions together so I can decide whether I need database software at all - if I need it for the last project, then maybe I'd go ahead and get it and put my personal movie list in a database as well, for example.

Any input or advice appreciated.

Bobo Fett, you should have a look at VBA and RegEx, would make your life easier.

Movie collection and database have excellent programs that will simply output all that information and way, way more into excel-sheets if needed. I've done that and it was relatively painless. I can't remember the program though, but I can find it if necessary.

Your NHL-calculations seem also like you're doing a lot of manual work for things that can be done easier.

http://www.hockey-reference.com/leag...3_skaters.html click csv, copy it and save it as a text-file. Import that into Excel. It still requires heavy manual editing for sure.

The rest of your problems seem pretty complicated but really it just comes down to basics. Keep your individual season data separate and try to avoid copy/paste as much as possible and instead use functions to call data from the season data page.

If you have different seasons on different sheets, you don't need to do all that inserting of blank rows business to get them combined on one sheet.

1) create blank third sheet
2) paste all names from season 1 and 2 in column A.
3) Data tab > Remove Duplicates
4) VLOOKUP combined with IFERROR to pull in each season's data. IFERROR allows you to return blank or zero for players who don't appear in one of the seasons.

Bobo, I'll take a closer look over your post tonight or tomorrow but I wanted to leave you a few links/comments for the meantime.

http://www.hockey-reference.com/
http://awardsdatabase.oscars.org/amp...earchInput.jsp
http://www.the-numbers.com/market/20...rossing-movies

It would be pretty easy to rebuild your NHL sheet from scratch so that it is more flexible and easy to update. I can tell you that you are spending a lot more time on it than you need to. Vlookups and pivot tables will help you a lot. No need for database software.

I would strongly recommend not keeping players' stats on one row. It's much easier to work with it in this format:

Player Name | Year | Goals
Player A | 2013 | 20
Player A | 2012 | 25

I can probably help you some more with this, just take a look on that site I linked and tell me what you think as far as available data.

-----

Interestingly enough I have built pretty big tables that cover the data you want in your two projects. (I work for a studio) It's tough. None of the popular sites (IMDB, BoxOffice mojo) have good data aggregation and their data is occasionally inaccurate. To use those sources, assuming you have a large title list, you will need to have access to a dump or know how to scrape.

Check the numbers link and see if that's good enough for you. It will have some of what you need but not all.

I can't speak to the website stuff unfortunately.

You guys are awesome; some great suggestions, thanks!

Yeah, I've always thought about looking into VBA more, but never seem to find the time.

Damn, never even thought to look for software specific to this. Looks like there's lots out there; a quick search found me this, for example:

http://www.techsupportalert.com/best...n-software.htm

Yeah, there's no question that I'm doing manual work that I don't need to, hence my questions.

I've come across hockey-reference in the past, but didn't realize what a nicely formatted stats history function they had, thanks!

Looks like CrazyEyez had the same idea as you:

That's awesome; never even thought of putting the seasons in different sheets.

When I went to the awardsdatabase.oscar site and clicked on the "Browse Statistics" link, that is a lot like the kind of thing I want to develop. I'm looking to create a database of award-winners - probably Academy Awards & Golden Globes - and be able to produce a number of lists from it. The more I think about it, I don't think I need to have the data able to be manipulated by website visitors. I'd just have it in my own spreadsheet, and then produce the lists I want and publish those. Although I'm not sure about the initial historical data entry, I don't think the data entry each year really needs to be automated - there aren't that many awards given out each year.

I think my biggest challenge with this project is going to be determining how to organize the information. I suppose each award nomination having its own record (row in a spreadsheet) should work, as long as I make sure I'm using the same names when they get entered in multiple places. IE that performers' and movies' names are always spelled the same way.

Do you think I need a separate row for every stat or will all stats for a year on one row work? For example, adding | Games Played | Assists, etc., to the above example. And I guess I'd want separate rows for when a player is traded so I can separate data from each team he played for.

I think my list will be simple enough that I could even manually enter the data if I had to. Not sure if there will be a data source with all AA & GG winners for the past 80+ years that would make for an easy import.

If you mean NHL, I think that has everything I'll need.

Unless...I ever get to my ultimate goal, which would be to have access to game-by-game data, giving me the ability to break down a player's statistics within the season. IE, he scored 20 points in his first 18 games, but then only 40 in the remaining 64 games, etc. But getting that data might be too difficult, IDK.

I don't think I need to do that anyway.

I see now that hockey-reference offers game logs as well. Hmm.

http://www.hockey-reference.com/play.../gamelog/2013/

Ideally one row = one player's stats for an entire season and only that season. You want as granular as possible and then you can build in keys for year, team, whatever else you want. The reason I want to structure it that way is to make it easily pivot-able. Are you comfortable with pivot tables?

I'll make you a sample file showing you how I would structure it using full season data later tonight or tomorrow.

---

The game log scenario is one where it'd be nice to be able to grab a bunch of csvs and just mass load them instead of c/p. If you want to get creative you can even tie the game logs to the regular season stats and what not. It just depends on how much time you want to spend on this.

You can do the full season thing really fast.

Never used one, but I really need to learn.

Edit to add: Just started with the Wiki page about them, and now I'm feeling rather foolish for never having delved into them before.

I'm sure I could muck through it, but if you have the time, that would be awesome, thanks!

It's no problem. It took like 5 min to make this lol. (Warning though, the data is not 100% clean)

https://www.dropbox.com/s/gs4or6luxu...%20Hockey.xlsx

I think if you can learn pivot tables/vlookups back and forward then you will be ready to move on to some other stuff.

If you don't already know, there is a stats/data package for excel that will let you do multiple linear regression, histograms, etc. Also excel sheets can hold just over 1 million rows. So as long as you keep it under that you will be fine.

If you have any other questions, I'm happy to help. Sadly Excel is something that I am way too familiar with.

Wow. Be it 5 or 15 minutes, a quick glance has me very impressed - can't wait to have a more in-depth look later, and obv I need to learn Pivot Tables and Vlookups as you've suggested.

And I got my most important answer of all - definitely no database software required.

Thanks!

Plenty of 10-15 minute tutorials on YouTube detailing how to structure pivots.
Watch one

Yeah, I'm going to start Googling and learning. Looking at it, I'm pretty sure I'll be able to pick it up quickly. It's just one of those things I've always assumed I didn't really need so I never bothered. Looks like I was wrong.

can I submit an equation and have excel solve for a variable? I couldn't find a step-by-step anywhere and I have a professor that makes me do all these backwards-ass algebraic equations for my flipping health.

I don't think so because I remember trying to do it once. I did make a worksheet that allowed me to plug in both the original formula and what I thought was x to make sure I was solving correctly though.

Goalseek or the Solver Plugin, the latter is more sophisticated, but the former is quite intuitive and pretty easy to use.