+ Reply to Thread
Results 1 to 6 of 6

Matching values in two columns - one column has duplicates

  1. #1
    Registered User
    Join Date
    01-07-2015
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    3

    Matching values in two columns - one column has duplicates

    Greetings,
    I am dealing with a large data set of student scores. Both students and teachers are tied to unique course IDs that are shared between the two groups of users.

    The student course ID column contains duplicates (since multiple students take the same course). The teacher course ID column contains no duplicates. Using that course ID, I want to be able to match each teacher's name with each student they teach.

    My end goal is to be able to use this information in a Pivot Chart/Pivot Table in order to show score data. I want to have the teacher's associated with the correct students before I do this. It's my understanding that you should not have any blank spaces in your table prior to creating the Pivot table, but there's a good chance I'm making this overly complicated!

    I've attached an example sheet to show what I have now and what I'd like to achieve. Any help you all can provide when it comes to the formula's needed to do this would be greatly appreciated.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Matching values in two columns - one column has duplicates

    c4=
    Please Login or Register  to view this content.
    See the yellow cells in the attached file.

    I noticed you used merged cells in A22.

    Don't use merged cells, you get in trouble with it, sooner or later.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Matching values in two columns - one column has duplicates

    Try

    =INDEX($C$4:$C$8,MATCH(B4,$D$4:$D$8,0))

    in E4 and copy down.
    Martin

  4. #4
    Registered User
    Join Date
    01-07-2015
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    3

    Re: Matching values in two columns - one column has duplicates

    Many thanks to you both.

    Both solutions produces the desired results. Would there be circumstances in which I would want to use one over the other?

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Matching values in two columns - one column has duplicates

    Vlookup only works if the data is in the column from left to right.

    You can always use index, match (for the rows), match (for the columns).

    I read on the forum that with very long data, the index / match solution is much quicker.

  6. #6
    Registered User
    Join Date
    01-07-2015
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    3

    Re: Matching values in two columns - one column has duplicates

    Good to know - thanks again - very helpful and responsive forum.

+ 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. Replies: 4
    Last Post: 07-15-2013, 01:37 PM
  2. [SOLVED] Matching duplicates from 2 columns and assigning price from 3rd column.
    By hardcoremark in forum Excel General
    Replies: 4
    Last Post: 08-03-2012, 09:43 AM
  3. Replies: 2
    Last Post: 12-30-2011, 07:30 PM
  4. Replies: 8
    Last Post: 12-25-2011, 08:39 PM
  5. matching values in columns that contain duplicates
    By jellybean in forum Excel General
    Replies: 8
    Last Post: 08-14-2006, 09:20 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