merging data from 2 spreadsheets

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 

0
JB
10/24/2008 3:40:23 PM
excel 39880 articles. 2 followers. Follow

3 Replies
157 Views

Similar Articles

[PageSpeed] 22

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

On Oct 24, 4:40=A0pm, "JB" <somehow@somewhere> wrote:
> Hello
> I work in a school and often export or collect data into spreadsheets of
> student grades and assessments =A0etc.
> 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) th=
en
> when the students are in the exact order of rows, I copy the columns of d=
ata
> i want from one file into the other. This is very tedious and making me g=
o
> 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

0
pashurst (2576)
10/24/2008 4:34:57 PM
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.


-- 
Thanks,
Shane Devenshire


"Pete_UK" wrote:

> 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
> 
> On Oct 24, 4:40 pm, "JB" <somehow@somewhere> wrote:
> > 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
> 
> 
0
10/24/2008 8:33:01 PM
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



"Pete_UK" <pashurst@auditel.net> wrote in message 
news:62a5205a-32e4-42eb-b388-87e15cd3d021@r66g2000hsg.googlegroups.com...
> 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
>
> On Oct 24, 4:40 pm, "JB" <somehow@somewhere> wrote:
>> 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
> 
0
JB
10/27/2008 9:56:06 AM
Reply:

Similar Artilces:

merging
Is it possible to create a document in publisher (mine is a certificate) and then merge the desired information from a select querry in access? Hi Tina (sttpreston@clyde.k12.oh.us), in the Microsoft� newsgroups you posted: || Is it possible to create a document in publisher (mine is || a certificate) and then merge the desired information from || a select querry in access? Yes, you can. Which version of Publisher and Windows are you using? -- Brian Kvalheim Microsoft Office Publisher MVP Official Publisher MVP Site: http://www.kvalheim.org This posting is provided "AS IS" wit...

Merge same transaction
I've had transactions that I've placed in my register with detailed info in the memo area only to have the same exact transaction download with no info and unrecognized as the same transaction. Traditionally I've copied and pasted the info into the downloaded transaction and then deleted my transaction. This is a real PAIN... Is there a way to tell Money that these two separate transactions are the SAME transaction and then have it merge them? It works exactly as intended when Money recognizes the transaction that's already in the register and then merges them beautiful...

look up question #2
I have a list of people with their system rights in the next column by group. Each person may have more than one type of right in the list as follows John Smith Edit Joe Doe Submit Cathy Dory Edit John Smith Submit As you can see above, John Smith has both edit and submit rights on this list. What I am looking for is a formula where I can look down the list of names and find all instances of John Smith and then it can tell me if John Smith ever has edit rights or ever has submit rights. What I would like it to do is return a 1 if he has edit rights a...

creating an address data base #2
I would like to create a database of clients, and when i type ne invoices i would like to select them so the address etc will fill int the address block automaticly -- adspreadboroug ----------------------------------------------------------------------- adspreadborough's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1565 View this thread: http://www.excelforum.com/showthread.php?threadid=27192 ...

Importing data from Maximizer
Hi: What experience has anyone had with importing Maximizer data into MSCRM using either the Data Migration tool, Scribe, or a combination of both? Scribe don't handle this seamlessly, you apparently need to export to a .csv file first. Is this still quicker using this process with Scribe, than with the DMF? Many Thanks Cathy Allington Hi, We are also looking at migrating Maximizer into CRM. If you would not mind, could you please let me know if you find anything more on this. I would truly appreciate it. Shauna skoppang@shaw.ca "Cathy Allington" wrote: > Hi: > ...

Copying Cells #2
How can I copy a range of cells (some of which are filled with gray) from one worksheet to another worksheet without deleting any data that may be in the same range on the second sheet. Say the area is from A1 thru B2. Select A1 thru B2 on the first sheet and: Edit > Copy then select cell A1 only on the second sheet and: Edit > Paste Special > Formats. -- Gary''s Student - gsnu200808 "DJ" wrote: > How can I copy a range of cells (some of which are filled with gray) from one > worksheet to another worksheet without deleting any data that may be in ...

Word mail merge
After printing a document there exists a activity with subject "Word Mail Merge" to the contact with status completed. Is there a possibility to change the subject name or to make a link to the original Word document? R, Joop. I think that the subjct name is hardcoded. The issue of only adding this paltry information to the activity and not showing what document was sent is a problem many have asked about. Microsofts workaround to this glaring gap in functionality is to then add a copy of the word document to the record using notes. Totally unworkable if you have just mail merg...

Merging 2 Companies into 1
Are there any guidelines or outline for what tables you need to look at to gather the various information you need in order to merge 2 companies together as well as issues to consider when undertaking such a project? It is a project and a half. I got approval from a major client yesterday to proceed with just this project. You literally need to look at all of them! I understand that MS Professional Services will do this project for you. In my case the client cannot be down and has too many 3rd party products for us to pass it off. When does your project need to be completed? -- Ri...

Outlook problem!!! #2
Hullo folks. When i try to answer to group on some topic and then press send i get a message window where you can say yes, no or cancel, but there is absolutely NO text in the box what so ever. And i cannot click anywhere in the box to make my post send. What gives? Best Regards Silver. On Wed, 10 Aug 2005 12:42:22 -1000, Silver <dsl46016@vip.cybercity.dk> wrote: > Hullo folks. > > When i try to answer to group on some topic and then press send i get a > message window where you can say yes, no or cancel, but there is > absolutely > NO text in the box what s...

How do I use 2 sets of data in a chart
both sets of data are in the same workbook, but on two different worksheets. Hi, Have read of Jon's page http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html Cheers Andy Skeety wrote: > both sets of data are in the same workbook, but on two different worksheets. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

adding data from multiple spreadsheets into one chart
I need to put information from 4 different spreadsheets into one graph. Can someone please help me? Produce a graph from your first spreadsheet. Copy the relevant data range from the second spreadsheet, then select the graph and edit/ paste special/ new series or new data points. Similarly for the third and fourth sheets. If you have difficulties getting exactly what you want that way, you can use edit Source Data in the graph to add an extra series or to change the X or Y data range for an existing sreries. -- David Biddulph "Marjory" <Marjory@discussions.microsoft.com&g...

mail merge/merging 2 address lists...
Is there a way to merge two of your address lists in Publisher 2002? I have two address lists in my data base folder and would like all the addresses merged into one as there are some addresses different in each and some the same. Thank you. Robin Hi Robin (vicary@kconline.com.NOSPAM), in the Microsoft� newsgroups you posted: || Is there a way to merge two of your address lists in Publisher 2002? || I have two address lists in my data base folder and would like all || the addresses merged into one as there are some addresses different || in each and some the same. No, you cannot. You nee...

merge and compare
We have two versions of the same work sheet from differnt dates. Some of the cells have been changed in the latest version and we need to compare which cells have had changes and selectively merge the two. Is there any hope??? jimired wrote: > We have two versions of the same work sheet from differnt dates. > > Some of the cells have been changed in the latest version and we need > to compare which cells have had changes and selectively merge the two. > > Is there any hope??? Hi have a look at http://www.cpearson.com/Zips/Compare.ZIP An add-in which compares two worksh...

Any way to shift data axes?
Helo all, I am having problems getting some data into the format I need. I'm wondering if this is even possible actually. I'm sure it is, but I'd prefer to keep things simple. Data is coming in from a linked Excel table in a format like the following MRC VEH 1 2 3 4 5 101 AAA 12 34 56 78 90 101 BBB 12 34 56 78 90 101 CCC 12 34 56 78 90 102 AAA 12 34 56 78 90 102 BBB 12 34 56 78 90 102 CCC 12 34 56 78 90 103 etc... MRC and VEH denote the cost centre and vehicle types. For those combinati...

How can I get bar codes w/ Excel & mail merge?
I want to send out a bulk mailing with large postcards. I want the addresses printed on the postcards via mail merge. I am using Excel, but don't see where or how to add the bar code to the address, which would save me a lot of money. Can you help? >-----Original Message----- >I want to send out a bulk mailing with large postcards. I want the addresses >printed on the postcards via mail merge. I am using Excel, but don't see >where or how to add the bar code to the address, which would save me a lot of >money. Can you help? >. > hi, I not entirely...

Merging worksheet into MS Word mail merge
I am merging a letter and an Excel worksheet. When I merge an Excel field that contains $ and commas separating the hundreds and thousands, the $ and the , do not show up in the merged letter. Any suggestions on how to solve this problem? -- Briggs Hi Instead of just referring to the cell e.g. A1 use =TEXT(A1,"[$$-409]#,##0.00") -- Regards Roger Govier "Briggs" <Briggs@discussions.microsoft.com> wrote in message news:2F326201-A50F-45E5-9767-4C189ACA5529@microsoft.com... > I am merging a letter and an Excel worksheet. When I merge an Exc...

Error in Mail Merge when selecting Edit Individual Labels
We are noticing an interesting error when trying to perform a mail merge within CRM and Microsoft Word. We have created a view in contacts and we perform all the typical mail merge functionality using the Labels option. At the end, instead of printing the labels without any edits, we click on “Edit Individual labels” and we get the following error… C:\Program Files\Microsoft Office\Office12\OUTLOOK.EXE has encountered a problem and needs to close. We are sorry for the inconvenience. We are on CRM v4 Update Rollup 8 using Outlook 2007 and the CRM Outlook (Online only) client. Has an...

importing data using a macro #2
I'm trying to import ten sets of data in Excel. I click on import external data and then choose a file and press next. I add a space to separate the data into two columns and then press finish. I was wondering if it would be possible to use a macro to import the data. Possibly import the first one and then have the macro import the next 9 in succession. The files I import are always in succession, but always have slightly different names. For example, the data I collect today will be saved as 718cr1, 718cr2, 718cr3, etc. The last number represents the trial number and the fir...

Transfer data from XML to Stream?
Hi all, I have a COM component that receives XML documents from MSMQ. Before I process the final documents and pass it to my application, I want to modify the format a bit, as in create a name/value pair structure from an element structure or rename certain fields. I'm using XmlTextReader to read over the original doc and I'm using XmlTextWriter to create a new XML file that I essentially need to load up again into an XMLDocument object. I'd rather write the modified XML to a stream local to my running instance of the COM component rather than create another physical file then ...

Whats the formula to look at data in precentages in fixed periods
how do I calculate a continous percentage return of two data points at fixed intervals? example : data set={12,50,03,59,22,....} , fixed interval=2periods. [(12-03)/12], [(50-59)/50], [(03-22)/03],....I'd like to do this in Excel 03 "donald" wrote: > how do I calculate a continous percentage return > of two data points at fixed intervals? > example : data set={12,50,03,59,22,....} , > fixed interval=2periods. > [(12-03)/12], [(50-59)/50], [(03-22)/03], Assuming data is in A2 down: 12,50,3,59,22,... In B2: =(A2-A4)/A2 B2 formatted as percentage, then copi...

how to split data in a range to many ranges
I have data in a range, like 1,2,3,4,5. I want to split each of them to five different ranges. How to do it. thanks. -- Paul Data|Text to columns Delimited by commas Looks like it should work. Paul wrote: > > I have data in a range, like 1,2,3,4,5. > I want to split each of them to five different ranges. > How to do it. thanks. > > -- > Paul -- Dave Peterson Thank you, Dave. I made it. "Dave Peterson" <ec35720@netscapeXSPAM.com> ???????:421882CD.22F31915@netscapeXSPAM.com... > Data|Text to columns > Delimited by commas > > Looks...

look up a selection of data
Hi I would like to transfer or look up data when keying in a reference number and bring up all the data regarding the ref number Eg Cell A1 = 101, B1 = John and C1 = january Cell A2 = 102, B2 = Mark and C2 = january Cell A3 = 103, B3 = Sam and C3 = February Cell A4 = 101, B4 = John and C4 = March Cell A5 = 101, B5 = John and C5 = June When I key in Ref 101 in a chosen cell (eg.A10) then the display should have all the details Cell A11 = John, B11 = January Cell A12 = John, B12 = March Cell A13 = John, B13 = June I assume A11, A12 and A13 should also have formulas to generate data but I c...

Pasting into merged cells #3
I want to copy information from an area that doesn't have merged cells to an area that has merged cells. When I do this I either get an error message or when I use the Paste values and number formats it only pastes every other entry. Is there another way to paste that I don't know of that will paste into the merged cells? -- Thanks, Nikki Hi best would be to get rid of merged cells. You could of course paste cell by cell "Nikki" wrote: > I want to copy information from an area that doesn't have merged cells to an > area that has merged cells. When I do ...

Back ups #2
Is there a simple way to Back up Everything in Outlook all at once. I want to use this for business and can not lose such things as Journal entries, phone conversation notes or phone numbers. Would like to be able to back up every day, but needs to be simple. Susie On Wed, 6 Sep 2006 11:17:01 -0700, Sue Robinettt <Sue Robinettt@discussions.microsoft.com> wrote: >Is there a simple way to Back up Everything in Outlook all at once. I want >to use this for business and can not lose such things as Journal entries, >phone conversation notes or phone numbers. Would like t...

Migrating my data from OL2000 to OL2003?
Since MS says (MS Press book) an 'older' OL version can't be present when OL2003 is installed, does my OL2000 data somehow(?) become included (migrated) with my OL2003 when I install OL2003? yes, it will 'migrate' (in actuality, it stays put - outlook accesses it) I would recommend finding your pst and making a copy, for insurance. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Outlook Tips: http://www.outlook-tips.net/ http://www.poremsky.com - http://www.cdolive.com Expert Zone http://www.microsoft.com/windowsxp/expertzone Searc...