So much for the 'painless' way of getting the information into Excel. It
might be possible to use some OCR tools and scan them into .txt files to be
read in, maybe not, but even so, it might take some code to read and separate
the data the way you need. Something tells me someone is going to be doing a
lot of typing for a while - got any handy undergrads laying around hungry for
some extra credit?
Here's how I'd probably start out - I would put ALL information for ALL
results on a single sheet - assuming they'll fit within 65535 rows.
Sit down and plan this out a little before starting out. Besides the
obvious information to be entered such as student ID/Name, scores, etc. You
will want to also consider all of the ways you may want to break up the data
later for different analysis. So even though maybe the fact that a series of
results were taken by 5th graders is only implied by the pile they are in, if
you plan on analyzing by education level later, then you'll want to
explicitly have an education level column for each result. Same for
ethnicity, native language, etc. Consider other things you may not think you
need now, but may later, such as date of test.
What you want to do initially is be painfully detailed in your data
gathering, so much so that you don't have to go back and redo even a small
part of it. Might even have a column to correlate the data on the row with
the source document for it.
Think of shortcut ways to help make things easier and more consistent. For
instance, in the columns for ethnic origin or native language, you might set
up Data Validation containing all possible groups so that the data entry
clerk can just choose from a list which will make it a little easier and
definitely make it easier for analysis later.
Having everything on a single sheet will make it easier to tell what you've
done and what you haven't and see gaps in data. It will also make it easy to
use Excel functions like VLookup to move groups of data to other sheets to
get it separated out. But Job-One is to get the data into the workbook.
Now, I'm guessing that 8 years worth of results is a whole big bunch. You
won't want to lose anything halfway through this project. Make sure you back
that file up regularly - preferably to either a removable media or at least
to another computer so that catastrophic failure of the system doesn't cost
you all of your work. Having it on removable media like a thumb drive or
flash card can even help protect against catastrophic damage at the facility.
Now once you've gotten everything into the workbook and have a plan on how
you want to look at all of the data, then we can start on that phase of it.
You can be planning some of it while data entry is in progress - but until
you know specifically what data you have to work with, it's going to be hard
to finalize that part of the game plan.
It might even turn out that this will be better suited to being processed by
a database application like MSFT Access. Having all the informatioin on a
single sheet with each student/result as a single row entry will make getting
the data from Excel into the database a classic "piece of cake".
Once you get to that stage, if you'd like to see if I have any more help to
offer - and I may or may not - feel free to get in touch with me via email at
2kmaro @ dslr.net (remove spaces) - just kind of remind me where this
conversation started.
Good luck. Aspercream is good for fingers worn out through days of endless
typing <g>
