+ Reply to Thread
Results 1 to 14 of 14

Cross-referencing Lookup?

  1. #1
    Registered User
    Join Date
    07-28-2008
    Location
    Moncton
    MS-Off Ver
    2003
    Posts
    53

    Cross-referencing Lookup?

    I am trying to match a person's name in the A column (A3) with particular course requirements in Columns B through to BB (ie. B2). Not every person will have taken every course. I have the master list of data on another worksheet with 4 columns of data: A-Student Name, B-Course Name, C-Final Mark, D-Additional Notes.

    What I would like to do is have Excel find the student name on sheet one as well as each of the courses listed from B-BB and check sheet two (master data) to find if the student name and course name appear on the same line. If it does I want it to return the value in column C (the final Mark).

    I thought that I could do this with VLOOKUP but it only works with the left most column correct? I saw something about a VLOOKUP2 but couldn't find that option in Excel 2007.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Hi and welcome to the board

    not having XL on my PC now I can't give you a detailed solution, but you might find some help here

    Cheers

  3. #3
    Forum Contributor
    Join Date
    08-28-2006
    Posts
    280
    I'm not too clear on what you're asking but if you wish to lookup from right to left which is backwards for Excel, you may want to look at index/match instead of vlookup.

    Dean

  4. #4
    Registered User
    Join Date
    07-28-2008
    Location
    Moncton
    MS-Off Ver
    2003
    Posts
    53
    Think of the first sheet as a gradebook with student names down the first column and the list of courses they have to take along the other rows. On the second sheet in all the master data. The first column has the student name, the second one has all the courses that student has taken so far and the third column has the final mark for each of the courses.

    I want to cross-reference the student name and the course name on the first sheet and have it check to see if the student has taken the course on the second sheet and return the mark value to the first sheet where the course name and student name intersect.

    Did that make sense?

  5. #5
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Maybe you can post a small sample of your data and of what you want to do? (see "manage attachments" in the Reply to Thread screen)

  6. #6
    Registered User
    Join Date
    07-28-2008
    Location
    Moncton
    MS-Off Ver
    2003
    Posts
    53
    I put together a small version of what I am trying to do since the original document contains almost 17000 rows of data.

    So what I would like Excel to do is this (for example): From the first sheet I want it to take the student name from column A and the course name from the column(s) and check on the second worksheet to match both that student name and course. If it finds a match and the mark is 60 or higher it is to put that score on the first sheet where the student name and the course name meet. If it isn't higher than 60 it continues to search until it hits the course name with the right mark or it runs out of data.

    I hope that makes sense when you see the sheet.

    Thanks,
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Students gradebook

    Here are two option (see encl. file)

    Alt.1: Sumproduct formula.
    Alt.2: PivotTable. Needs to be update by right click on the table, Refresh data.
    In both cases, 0 values has been hidden with Conditional formatting, color white.

    Hope it helped
    Ola



    ...I don't know if this is also interesting to see.
    Attached Files Attached Files
    Last edited by olasa; 07-29-2008 at 09:52 PM.

  8. #8
    Registered User
    Join Date
    07-28-2008
    Location
    Moncton
    MS-Off Ver
    2003
    Posts
    53
    Thanks, Ola, for taking a crack at it for me. I have a couple of questions to make sure I am understanding what I am seeing correctly.

    In Alt 1, would I have to know where in the AC_Hist worksheet each of the courses were? Because with 17000 lines of code, that would take me a long time to hand code each of those formulas. I see the absolute values in there I am just wondering how Excel knows how to find the match between student and course when it is not as predictable as my small example is here.

    In Alt 2, I see that Bob Smith got a 105 in Math 120. I had put in two marks for this class for students who fail and then retake the class and pass. In this case I would want Excel to skip the one that is under the 60 condition and search until it finds one over 60 or just gets to the end of the data in which case it returns a blank.

    I appreciate the help as I am still very new to the advanced formula strings and will check out the link to arrays that you gave as well to see if that holds some answers.

    Again, thanks for your efforts. It's nice to have a direction to go in.

  9. #9
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Final grade summary. PivotTable

    One second thought. Since there are 17.000 lines in the list...
    Alt.2: a PivotTable - is by far the best option. It will be much faster.
    Alt.1: Array formulas are much slower.

    Yes you are right 105 is not very good. I changed the PivotTable so that now Only the Maximum value is shown.
    Meaning, if a student takes the course twice - to increase his/hers final score - the PivotTable will only show the highest score (I suppose some students do that).

    Brief about PivotTables:
    * To update: Right click on the PivotTable and select Refresh data
    * To change the data range: Right click on the PivotTable and select Wizard... and then <Back
    * I also enclosed a link to the best free tutorials Video and Text about Pivottables

    Hope it helped
    Ola

    and...I made a column called Passed?. You can delete it if you want.
    + I made an Autofilter, which might be handy. The Average will change based on the filter.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-28-2008
    Location
    Moncton
    MS-Off Ver
    2003
    Posts
    53
    Thanks again, Ola. I tried the first Alt last night and it did take my more powerful desktop quite some time to calculate the sheet so I am glad there is another option out there. I think this one will work on the section of my worksheet that has clearly identified requirements and pass marks. I like the passed column as well.

    If I might ask another question on the same topic? On the same sheet with these required courses I also have a section where students have to take a number of electives at the Grade 12 level. Is there a way for Excel to scan the list to find courses not already listed in the first section we have done (English, Math, Science 120) and return the name of the course and the mark over 60 to the first work sheet?

    Thanks again for all your efforts. I am going through the tutorials on pivot tables now and they are quite powerful.

  11. #11
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Next question

    Thanks. Happy you like PivotTables. They are, great.

    Querstion:
    "I also have a section where students have to take a number of electives at the Grade 12 level"
    "Is there a way for Excel to scan the list to find courses not already listed in the first section we have done (English, Math, Science 120) and return the name of the course and the mark over 60 to the first work sheet?"
    Most likely, yes. Something like: Have the student taken all required courses - comparing with the required list.
    Excel could either list what the student have taken or haven't taken, compared to that list.

    Send an example how you would like it, and explain the short rules.

    Ola

  12. #12
    Registered User
    Join Date
    07-28-2008
    Location
    Moncton
    MS-Off Ver
    2003
    Posts
    53
    I have put the full sheet together with the requirements and included the data for one imaginary student for testing purposes. I included in a text box what the required rules would be for the last sections.

    Thanks again for taking a look at this. The learning curve can be a bit steep but I am truly amazed at the amount of things that Excel can do!
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    ...

    I think someone from your country is better equipped to look at the spreadsheet.
    I feel I have so many questions that someone from your country would already know about.

    ? the 12grade + pick out 4 12grade + some mandatory all the rest are electives
    ? the Reading/Writing which seams to have a different way of grading. Is this one course or two courses.... If one, are both grades needed to pass...
    ? is ELPA part of the electives or mandatory or ...and a few more questions...


    Tip: make a fresh post, not to scare someone off.

    Good luck
    Ola


    I'm not sure it will help but I have attach the file - as far as I got.
    Do mention that there are 17.000 rows, and that calculations should be minimized.
    For speed: I would leave just 1 row with formulas and copy paste the rest as Values. Sheet: Input, Columns:Calculations
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    07-28-2008
    Location
    Moncton
    MS-Off Ver
    2003
    Posts
    53
    Sorry to hear that, Ola as you have been a great help so far. The ELPA is short for the English Language Proficiency Assessment which is a two part exam that all students must pass before graduating. The AA and ED are just Appropriate Performance and Experiencing Difficulty. I am really not too concerned about that part as I think VLookup will do that without too much trouble.

    As for someone from my country looking at the sheet the problem is that our education system is done by province and ours is rather small so the odds of someone else knowing the system specifically are pretty low.

    As for the mandatory versus the Grade 12 courses. The students are required to get 17 credits. These credits are a combination of the mandatory courses that we already looked after with the pivot tables from above and the electives. The electives are divided into two categories. Grade 12 electives one of which must be whatever Grade 12 English they took (since they have a choice of which one its considered an elective) and then any other 4 courses that has the grade 12 number designation (120 for example). The other category is any other elective at the grade 11 or grade 12 level that hasn't been already used in one of the other sections.

    I don't know if that gives you (or anyone else readying the thread) any more to go on so I'll wait to see if you have anything to add before I start a new thread.

    Thanks again for all your efforts so far.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Cross Referencing two spreadsheets? (vlookup)
    By AnthonyC in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-10-2008, 04:35 PM
  2. Cross referencing 2 workbooks...
    By yeamans17 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-23-2008, 10:37 AM
  3. LOOKUP - referencing cells
    By Raoul Duke in forum Excel General
    Replies: 3
    Last Post: 02-26-2008, 09:40 AM
  4. Lookup referencing cell referencing range
    By cmcconnehey in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-15-2008, 06:19 PM
  5. cross referencing
    By n-finite in forum Excel General
    Replies: 2
    Last Post: 11-24-2007, 06:30 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1