Hello. I have two sheets:
- Sheet #1 is a list of programs at our university with the following columns (and sample values)
- Column "College" (e.g., PB, which is code for College of Psychological and Behavioral Sciences)
- Column "Program" (e.g., Applied Behavioral Sciences, B.A.)
- Column "Required" (e.g., ABS 400,ABS 401,ABS 305,ABS 300,ABS 340,ABS 309,ABS 331)
- Column "Electives" (e.g., ABS 435,LIBR 300)
- Sheet #2 is intended for a user to enter a course code (e.g., ABC 123) and see a list of programs that have that course as a Required course or Elective course.
On Sheet #2, I'd like for the user to enter a course code (e.g., ABS 400), then in the rows below, list the colleges and programs for which that course is Required or Elective.
The results may look something like this:
USER ENTERS: ABS 400
The rows below would then parse the information into three columns, with each college/program match appear in its own row:
PB | Applied Behavioral Sciences, B.A. | Required
PB | Clinical Counseling (Florida) B.S. | Required
PB | Clinical Counseling (Illinois), B.S. | Elective
UC | Counseling (Concentration) | Elective
UC | Adult Learning (Concentration) | Elective
The number of rows may vary based on the course code, but there would be three columns of information displayed if the course code is a match in any college/program (on Sheet #1) OR it says "No Match" if the course code isn't a required or elective course in any program.
I can imagine it being an INDEX/MATCH/COLLECT/JOIN kind of formula, which might be used in a hidden column, then that collected information from Sheet #1 (if not blank, which would result in a "No Match"), would display in each subsequent row in Sheet #2 (separated perhaps wherever a comma appears, if a comma is used to join the matches).
Thanks!
