I have two sheets: Sheet 1 that people use to submit requests for course redevelopments and Sheet 2 that has a record of all 1,000 courses (with a link to the "master" in our learning management system).
We are in the process of standardizing the names of our "masters". Essentially, they contain three pieces of information:
- Course Code (e.g., ENG 210)
- Modality (OG or on-ground, BL or Blended, OL or online)
- Weeks (6 or 10 weeks/wks,wk)
Here are some examples illustrating the inconsistency currently in how the "masters are named:
- ACC 202 Blended HP3 YrUp 12Wk
- CIL 500 Online 6Wk
- CSS-210 Blended 10Wk
- PPA 500 Blended 6Wk
- RLD 607 Blended 10Wk Master
You can see that sometimes a Course Code has a hyphen, sometimes it does not. The Modality is sometime abbreviated (OG) and sometimes spelled out (Blended, not BL). And Weeks is sometimes with a Wk (6Wk or 10Wk) or just a W (5W).
What I would like to do---to the best of our ability until the "master" names are standardized---is take the request in Sheet 1 and find the "master" name that currently exists for that course/modality/weeks.
So, for example, if a user enters in Sheet 1 that they want to redevelop PPA 500 (in the column "Subject + Course") and "BL-6w" (in the column "Modality"), I'd like for Smartsheet to find the "master" name that currently exists for that course (in this case, "PPA 500 Blended 6Wk") in the column "Program, College, Course" in Sheet 2 and inserts that value in the cell in Sheet 1.
The logic is this: In Sheet 1, I would have to use the Course Code (entered as "PPA 500") and the full Modality (entered as "BL-6w") but split via LEFT and RIGHT so Smartsheet can use "BL" and "6w" separately. Then, in Sheet 2, find the "master name" (in the column labeled "Programs, Colleges, Courses") that contains all three of those text strings: the Course Code (e.g., PPA 500), Modality (e.g., BL or Blended), and Weeks (6w).
This would be a nested IF, perhaps: IF the "master name" contains the PPA 500 AND the "master name" contains "BL" or "Blended" AND the master name contains "6w", assuming not case sensitive, THEN use put that "master" name in Sheet 1, ELSE say, "No Master Found".
I would have to look for BL or Blended (user enters BL, which is what the standardized names will use, but many "masters" still have "Blended"). I'd have to use the LEFT 2 characters of the Modality (BL, not BL-6w) and RIGHT of the Modality from hyphen on (6w, not -6w). And Smartsheet would pull look for the course that also contains 6w (whether it is in just 6W or 6Wk.
I have done the INDEX, MATCH, FIND formula to locate other text strings in other formulas, but not matches that contain a set of text strings ("PPA 500" and "BL" (or Blended) and "6w"). It should then exclude
- HSM 500 BL 6Wk (not PPA 500)
- PPA 532 Blended 6Wk (not PPA 500)
- PPA 500 Blended 10Wk (not 6W)
- PPA 500 Online 6Wk (not BL or Blended)
- PPA 500 Online 10Wk (neither BL or 6w)
But it would include any of the following naming versions of the course in Sheet 2:
- PPA-500 BL 6Wk
- PPA 500 BL 6Wk
- PPA 500-Blended 6Wk
- PPA 500 Blended 6weeks
When a match is made, the "master" name from Sheet 1 (it's actually also a hyperlink to the "master" in our learning management system) is inserted into the cell in Sheet 1.
I know this will be easier once all of our "master" names are standardized. However, this will take time to do So, for now, any thoughts?