Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Still struggling with Vlookup
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Still struggling with Vlookup
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. -- Thanks, Shane Devenshire "Sprowler" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Still struggling with Vlookup
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 "Sprowler" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Struggling to plot where I want | Charts and Charting in Excel | |||
Struggling IF value | Excel Discussion (Misc queries) | |||
Struggling for a solution | Excel Worksheet Functions | |||
Struggling Again With A VLOOKUP | Excel Worksheet Functions | |||
Help, I'm Struggling! | New Users to Excel |