Excel 2007 : Scorecard

[agreen77]

We want a "scorecard" on the front worksheet that will list a learner's name and their percentage of completion for each of their assigned training courses. This will be on sheet 1, on sheet 2 we will copy and paste the data from the report that our Learning Management System creates.

The scorecard should list the following:
Column A: Last Name
Column B: First Name
Column C: Username (hidden)
Coulmn C: Percent on course 1
Column D: Percent on course 2
Column E: Percent on course 3

Normally, I would think a vLookup would be best, however the raw data provided by the Learning Management System looks as follows:
Column A: Last Name
Column B: First Name
Column C: Username -- which is the system unique identifier
Column D: Course Title
Cloumn E: Course Percent complete
and then repeats the entry for each of the assigned courses, so it looks like this:

Adams, John, username (unique ID), course 1 title, 100%
Adams, John, username (unique ID), course 2 title, 0%
Adams, John, username (unique ID), course 3 title, 25%

Brown, Jane, username (unique ID), course 1 title, 0%
Brown, Jane, username (unique ID), course 2 title, 100%
Brown, Jane, username (unique ID), course 3 title, 50%

etc. etc. for all the thousands of learners.

In my head, the logic is to "get percent complete where course title = 'course 1 title' and username= value of data in C1" for each of the courses assigned.

I'm looking for the best way to accomplish this -- vLookup, Pivot table or VB code? Thanks in advance!

[Paul]

Hi Agreen, welcome to the forum. Take a look at the attached workbook and hopefully it meets your needs. The formula in D2 is simply copied down & right to F3.

=INDEX(Sheet2!$E:$E,MATCH($A2&$B2&D$1,Sheet2!$A:$A&Sheet2!$B:$B&Sheet2!$D:$D,0))

[agreen77]

Thank you, Paul! Your doc does just what I want mine to do!!!! You made my weekend!