+ Reply to Thread
Results 1 to 11 of 11

Combining two data sets

  1. 09-10-2011, 08:29 AM #1
    Teacher
    Teacher is offline
    Registered User
    Join Date
    10-04-2009
    Location
    Colchester, England
    MS-Off Ver
    Office 365
    Posts
    30

    Post Combining two data sets

    Hi All,
    This has been an excellent forum helping me streamline my department, I have another question.

    I have class lists and raw exam data I want to copy the exam data to the corrosponding students. Not all students that have are in the class list have data for the exam. I have been looking at Vlookup but cant seem to get away from #Ref error. I have attached an example.

    What I want to do is copy the UMS score and grade from the Exam Data sheet over to the class list in Column D & E.

    Many thanks for any help
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-10-2011, 08:47 AM #2
    arthurbr
    arthurbr is offline
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Combining two data sets

    In row 2 try


    UMS Score =VLOOKUP(A2,'Exam Data'!$A$2:$B$14,2,FALSE)

    Grade =VLOOKUP(A2,'Exam Data'!$A$2:$C$14,3,FALSE)

    Have a look at the VLOOKUP syntax in XL help
    Reply With QuoteRegister To Reply
  3. 09-10-2011, 08:55 AM #3
    stanleydgromjr
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Combining two data sets

    Teacher,

    In worksheet Class List, copy the following formula into cell D2

    =IF(ISNA(VLOOKUP($A2,'Exam Data'!$A$1:$C$14,COLUMN()-2,FALSE)),"not found",VLOOKUP($A2,'Exam Data'!$A$1:$C$14,COLUMN()-2,FALSE))


    Then copy D2 to range D2:E17


    You may have to change the lower range, $C$14, to the last row of data that will be available in worksheet Exam Data.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
    Reply With QuoteRegister To Reply
  4. 09-10-2011, 10:53 AM #4
    Teacher
    Teacher is offline
    Registered User
    Join Date
    10-04-2009
    Location
    Colchester, England
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Combining two data sets

    Hi Arthurbr,

    I have tried using yours and it works however for some reason when I copy this into my spreadsheet it returns a value of the row above.

    I had to change FALSE to TRUE as the names were not in the exact format.

    Ive atached the very basic spreadsheet.

    Cheers
    Last edited by Teacher; 09-10-2011 at 01:37 PM.
    Reply With QuoteRegister To Reply
  5. 09-10-2011, 11:23 AM #5
    arthurbr
    arthurbr is offline
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Combining two data sets

    And which is the problem in this book ?
    Reply With QuoteRegister To Reply
  6. 09-10-2011, 11:45 AM #6
    Teacher
    Teacher is offline
    Registered User
    Join Date
    10-04-2009
    Location
    Colchester, England
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Combining two data sets

    Sorry forgot people arent physic

    On the sheet "Test Results Core" colums W & X.

    Say for example Look at C6 her UMS score should be 60/100 and a Grade C but if you look at the "Exam Data" sheet it has actually selected the cells above this one so reports 40/69 and an E.

    It has done this for all of them for some reason.

    Thanks again for any assistance.
    Reply With QuoteRegister To Reply
  7. 09-10-2011, 01:10 PM #7
    arthurbr
    arthurbr is offline
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Combining two data sets

    I'm sorry, I can't seem to find the sheet you are mentioning
    Reply With QuoteRegister To Reply
  8. 09-10-2011, 01:39 PM #8
    Teacher
    Teacher is offline
    Registered User
    Join Date
    10-04-2009
    Location
    Colchester, England
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Combining two data sets

    Sorry I wouldnt let me upload it due to the size I have stripped it all back so its a bit smaller.

    This should work
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 09-11-2011, 06:50 AM #9
    Teacher
    Teacher is offline
    Registered User
    Join Date
    10-04-2009
    Location
    Colchester, England
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Combining two data sets

    Anyone got any ideas???

    Cheers in advance,
    Reply With QuoteRegister To Reply
  10. 09-11-2011, 09:08 AM #10
    arthurbr
    arthurbr is offline
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Combining two data sets

    Does this help ?
    See attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 09-11-2011, 12:11 PM #11
    Teacher
    Teacher is offline
    Registered User
    Join Date
    10-04-2009
    Location
    Colchester, England
    MS-Off Ver
    Office 365
    Posts
    30

    Re: Combining two data sets

    Hi It did work better but had to change back to FALSE and then adjust those few names that didnt match up.

    Many thanks for this
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1