Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extracting data from one sheet based on data in another - VLookup?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extracting data from one sheet based on data in another -VLookup?
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 On Feb 4, 2:11*am, des <livecamq.8.spamgourmet.com wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extracting data from one sheet based on data in another -VLookup?
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 On Feb 4, 2:28*am, Pete_UK wrote: 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 On Feb 4, 2:11*am, des <livecamq.8.spamgourmet.com wrote: 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.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extracting data from one sheet based on data in another -VLookup?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting data from one worksheet based on another - VLookup? | Excel Discussion (Misc queries) | |||
Extracting Data from another worksheet based on user input | Excel Worksheet Functions | |||
Extracting data from one sheet into another | Excel Worksheet Functions | |||
Extracting data based on content | Excel Discussion (Misc queries) | |||
Please help. Extracting data from one sheet to another | New Users to Excel |