extracting data from one sheet based on data in another - VLookup?

[des]

I have a worksheet Sheet1 with a list of about 1,000 students, their
student numbers and information about their timetables. A second worksheet
Sheet2, has a much shorter list with just names and student numbers. I want
to use the second list of names and student numbers in Sheet2 as the basis
for extracting timetable information from Sheet1.

The format of the data in Sheet1 makes it difficult - maybe impossible - to
use VLookup. It uses multiple rows for each student, like this -

Nam1 Nam2 No. Per Subj Rm Teacher
Cho Ed 483729533 A Math 132 Karim
Cho Ed 483729533 B Eng 317 Wong
Cho Ed 483729533 C Geo 210 Smith
etc. for 9 rows for each student to cover all 9 periods of the day.

I can use VLookup to locate matches based on the student number, but it
works only for the FIRST match for each student number. In other words, I
can extract information about the first period (Per, Subj, Rm) but nothing
about the remaining 8.

 

[Pete_UK]

One approach is to set up a new unique reference for each student and
each row in a helper column, like this:

=IF(C2="","",C"&"_"&COUNTIF(C$2:C2:C2))

Then you can make use of this as your lookup-vector. You would have to
use an INDEX/MATCH combination rather than VLOOKUP, unless you were to
insert a new column A in your Sheet1 and put the formula there (though
the references to column C would change to D).

Hope this helps.

Pete

 

[Pete_UK]

Sorry, I've just spotted a spurious colon and quotes. The formula
should have been:

=IF(C2="","",C2&"_"&COUNTIF(C$2:C2,C2))

Hope this helps.

Pete

 

[Herbert Seidenberg]

Excel 2007
Macro, Tables, Tested
http://www.mediafire.com/file/wym3ynmmhku/02_04_09.xlsm