Still struggling with Vlookup

[Sprowler]

Okay, I am still not getting it. So let me rephrase my issue. I have a
large spreadsheet of student test data. Each student record is identical
except to for the test data. Each student could have up to 4 records.

Bill Smith, History, 555
Bill Smith, Math, 222
Bill Smith, ELA, 330
Mary Johnson, History 333
Mary Johnson, Math 331

Each student has a unique student ID. How can I take this spreadsheet and
change it to reflect

Bill Smith, History, 555, Math, 222, ELA, 330
Mary Johnson, History, 333, Math 331


The order doesn't matter. There can but up to 4 tests. I just want to see
one horizontal line for each student with the scores listed horizontally.

Can you help me with this?

Thank you very much.

 

[ShaneDevenshire]

Hi,

You tell us there is a unique Student ID, but then you show us your data
without any Student ID? We need the student ID to solve this problem because
there could be many Bill Smith's.

 

[Arvi Laanemets]

Hi

Let's assume student names are unique (names are student ID's), test results
are always numbers, and there is none or one test result for given student
and discipline (otherwise you need some additional parameter like semester
ID in your table, and some changes in following design).

Add a worksheet Disciplines with an one-column (A:A) table (A1='Discipline',
with names of disciplines below header). Create a Named Range DisciplineLst
with source as
=OFFSET(Disciplines!$A$1,1,,COUNTA(Disciplines!$A:$A)-1,1)

Add a worksheet Students. A1='Student'. Enter Names/ID's of students into
column Students. Create a Named Range StudentLst with source as
=OFFSET(Students!$A$1,1,,COUNTA(Students!$A:$A)-1,1)

Let's assume the sheet with students test data has name Tests. Column
headers are 'Student' (A1), 'Discipline' (B1), and 'TestResult' (C1). Format
entry area for Student column as Data Validation list with
source=StudentLst, and entry area for Disciplines colum as Data Validation
list with source=DisciplineLst - it prevents faulty entries.
Define a Named Range TestTbl with source as
=OFFSET(Tests!$A$1,1,,COUNTA(Tests!$A:$A)-1,3)

On sheet Students, into cell B1 enter the formula
=IF(ISERROR(INDEX(DisciplineLst,COLUMN()-COLUMN($A$1),)),"",INDEX(DisciplineLst,COLUMN()-COLUMN($A$1),))
, and copy the formula to right for as many columns as much various
disciplines you'll have. All disciplines from DisciplineLst range are
displayed as column headers here.
Into cell B2 enter the formula
=IF(OR($A2="",B$1=""),"",SUMPRODUCT(--(INDEX(TestTbl,,1)=$A2),--(INDEX(TestTbl,,2)=B$1),INDEX(TestTbl,,3)))
, and copy it to range with as many columns as you copied the formula for
headers, and with as many rows as much students you assume to have.

It's all!

Arvi Laanemets