I am trying to create a 2-column table: child_username and parent_username, with the end result of linking parents to students on Moodle for a school of just under 1700 students and a few hundred families. For Moodle, the child_username is the student ID number (we use LDAP authentication for students and faculty) and the parent_username is the parents first and last names joined by a period and lowercase (Mark Smith -> mark.smith)
I have two separate spreadsheets of data exported out of two separate student management systems (we are in the middle of switching SMS's and the data was never complete to begin with) in the same excel file.
One (StudentFix) contains students' first and last names (separated and concatenated, for a total of 3 columns) and their student ID numbers. It is sorted by the concatenated names and the student ID numbers are to the right of the sorted column (as required by LOOKUP() )
The other (FamilyFix) contains firstName (student), lastName(student), First and Last Name (student- A_&" "&B_), FatherFirstName, FatherLastName, parent1 username (), MotherFirstName, MotherLastName, parent2 username (Code:=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2&"."&E2," ",""),"'",""),"-",""),"_",""),",","")))Code:=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G2&"."&H2," ",""),"'",""),"-",""),"_",""),",",""))
The result worksheet (that will eventually be saved as a tab delimited .txt to import into MySQL Moodle database) uses column D from StudentFix as child_username (no problem there), which I repeat (so each student has their studen ID number listed twice) andfor the first 1669 (number of students) rows andCode:=LOOKUP(StudentFix!C2,FamilyFix!C:C,FamilyFix!F:F)for the next 1669 rows.Code:=LOOKUP(StudentFix!C2,FamilyFix!C:C,FamilyFix!I:I)
The first 384 rows of both sets (so rows 2-385 and 1671-2054) return #N/A i the parent_username column. All of the data is formatted the same. There seems to be no difference between the two groups (those that work and those that don't). I would be happy to just go with it, but those 384 students' parents are probably going to feel left out...
Any help would be appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)