Many Administrators across the Faculty come across a problem whereby they need to match a piece of information to another piece of information quickly across an entire course with hundreds of users.

Examples of this might be:

  • Having a students mark and username, but needing the mark to be matched against the student ID.
  • Having paper titles in TurnItIn and needing those titles to be matched to the Student ID.
  • Matching Group Set names (blackboard) to student Usernames, when you only have the student ID.

These problems can be rectified using a mixture of Blackboard grade centre information, your existing information and the program “Microsoft Excel” using a function of excel called “vLookup”

Steps:

  1. Download the full grade centre from your blackboard course, this will include both the Username and the User ID of the users and act as a database which you will look up.
  2. Download the information you want matching (mark/essay title/group set name), as well as any other identifiers you have. For example you should have at least one of the following: User ID, Username, Name.
  3. Both of the above should have one piece of common information between them, either a matching ID number, or a matching username.
  4. Open a new excel file and set up as follows:
    1. Copy ALL of the information from Point 2 (above) into sheet 1,
    2. Create a new sheet and title it “index”
    3. Copy ALL of the information from Point 1 (above) into sheet “Index”
  5. On the sheet “Index” complete the following steps:
    1. Click the square above “1” and to the left of “A”, the entire sheet should be selected.
    2. In the box above the square you clicked (probably called A1):
      1. Click the box
      2. Type “Index”
      3. Click enter on your keyboard
    3. Locate the information which is common to both sheets (eg username) and copy the entire column.
    4. Insert/paste the column into Column A.
    5. Count the position of the column of the information you wish to be pulled into Sheet 1, where Column A = 1, Column B = 2, Column C = 3 etc. Make a note of this number.
  6. On the sheet “Sheet 1” complete the following steps:
    1. Locate the information which is common to both sheets (eg username) and copy the entire column.
    2. Insert/paste the column into Column A.
    3. Insert a new blank column into the Column B position. This is where your information will be imported to.
    4. In the first row you wish to have information you wish to be imported (usually Row 2), go to Column B (B2) and type the vlookup formula:
      1. =vlookup(
      2. Cell number of the known information (in this case column A and the row you wish to match).
      3. add a comma
      4. Index
      5. add a comma
      6. the position of the column you wish to bring in that you made a note of in step 5.5.
      7. add a comma
      8. false
      9. )
    5. the formula should look something like this: =vlookup(A2,Index,5,false)
    6. Click Enter on your keyboard and the cell should fill with the desired information.
    7. Return to the cell and double click the spot in the lower right corner to copy and paste the formula down to all the following cells.
  7. You should now have the information that you require in the column.
  8. To be certain that the information you have does not accidentally become corrupted while you are working in the spreadsheet, you should copy Column B in Sheet 1 and “Paste Special As Values” back into Column B, the formula will now be replaced by the raw data.

 

Addition: How to retrieve TurnItIn paper titles with Usernames:

  1. Enter your TurnItIn Inbox for the assignment you are interested in.
  2. Click “export”.
  3. You will now have a csv file containing paper titles vs usernames and can use the vlookup process to match the titles to the UserID.
Using Excel to VLookup Data Between Systems