merging data from 2 spreadsheets

[JB]

Hello
I work in a school and often export or collect data into spreadsheets of
student grades and assessments etc.
I often have to combine different lots of assessments from 1 or 2
spreadsheets into one and do this by arranging windows or 'comparing side by
side' and go down the list of students in both files, (sorted by name) then
when the students are in the exact order of rows, I copy the columns of data
i want from one file into the other. This is very tedious and making me go
blind!
Is there a better way of doing this?? Like a query or something. Anything?
I'm using Office 03.
Thanks for any help on this
Jen

 

[Pete_UK]

You need to have some data which uniquely identifies each student - a
name is not good enough on its own, but if you have a student ID
number in both files then you could make use of this. Then you would
just have a VLOOKUP formula which you can copy down your list of
students and it will automatically "pull" data for each student from
the appropiate column in the "sending" worksheet.

If you want a sample formula to use then please post back with some
details of how your data is laid out (which columns you use, sheet
names, filenames etc)

Hope ths helps.

Pete

 

[ShaneDevenshire]

Hi,

You should show us a sample of the data and what you want it to look like
when you are finished. However, you might want to look at the Data,
Consolidate command.

 

[JB]

Thanks for your reply.
Unfortunately sometimes there will be the Student's unique number in one
spreadsheet but not on the other but occasionally there will be.

As for the data that i want to combine in one spreadsheet for example
one file:
Surname
Firstname
Date of Birth
KS3 Eng result
KS3 Math Result
KS3 Sci Result

Second File:
Unique ID No.
Surname
Firstname
GCSE Eng Prediction
GCSE Maths Prediction
GCSE Sci Prediction

Sometimes there will be students added or removed in either file for one
reason or another. so the rows won't be identical.

thank you
Jen