Find Corresponding Value (in Pieces) in Other Sheet

Art Schneiderheinze

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:

1. Course Code (e.g., ENG 210)
2. Modality (OG or on-ground, BL or Blended, OL or online)
3. 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
• HOS-118-OG-5W-2D-Master
• 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-BL-6w-Master
• PPA 500-Blended 6Wk 
• PPA-500-Blended-B2Y-6W
• 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?

Paul Newcome

Try using a JOIN(COLLECT( statement.

 

=IF(ISTEXT(JOIN(COLLECT({Master Sheet Course Name}, {Master Sheet Course Name}, FIND([criteria 1], @cell) > 0, {Master Sheet Course Name}, FIND([criteria 2], @cell) > 0, {Master Sheet Course Name}, FIND([criteria 3], @cell) > 0))), JOIN(COLLECT({Master Sheet Course Name}, {Master Sheet Course Name}, FIND([criteria 1], @cell) > 0, {Master Sheet Course Name}, FIND([criteria 2], @cell) > 0, {Master Sheet Course Name}, FIND([criteria 3], @cell) > 0)), "No Master Found")

JOIN/COLLECT will join all cells together where all of the criteria in the COLLECT function is true. If you are able to specify criteria that will ensure only one course name matches, then it will display that one course name. By using the IF(ISTEXT(.................) part, you ensure that it will display whatever is pulled by the JOIN/COLLECT so long as it is actually text. If nothing is pulled, the JOIN/COLLECT would show as blank, so you use the "else" portion of the IF statement to say "No Master Found".

 

So basically what this does is says... If a name is found that matches all of this criteria, display it, otherwise display "No Master Found". 

 

You can also build in some checks to ensure if multiple course names are pulled, you are alerted so you can correct the issue. Something along the lines of a checkbox column with an

 

=IF(COUNTIFS(Same range and criteria as JOIN/COLLECT function) > 1, 1)

 

kind of formula in it. This will count how many cells within that same range fit that exact criteria. If it is more than 1, it will check the box. You can then use Alerts/Conditional Formatting to bring it to your attention, so you can address any issues.

Art Schneiderheinze

This is VERY VERY much on the right track!!! In order for the master to be identified, I've figured out there are four, possibly five criteria:

**********************

EXAMPLE:[Subject + Course]@row = ENG 210

[Modality(s)]@row = BL-10w

***********************

 

CRITERIA 1: The master must have the course subject (e.g., ENG)

LEFT([Subject + Course]@row, FIND(" ", [Subject + Course]@row) - 1)

 

CRITERIA 2: The master must have the course code (e.g., 456)

RIGHT([Subject + Course]@row, LEN([Subject + Course]@row)-FIND(" ", [Subject + Course]@row))

 

CRITERIA 3: The master must have right modality (e.g., BL, OG, or OL)

LEFT([Modality(s)]@row, 2)

 

CRITERIA 4: The master must be the correct number of weeks (e.g., 6w or 10w)

RIGHT([Modality(s)]@row, LEN([Modality(s)]@row) - FIND("-", [Modality(s)]@row))

 

Two problems:

Until we standardize the naming of all the masters, the modality in the master name could be BL or Blended, OL or Online, or OG. None would have On-ground, which is what OG stands for. So, if I just used Criteria 3---LEFT([Modality(s)]@row, 2)---it would likely not find the correct master because the correct master may use Blended, not BL. So, I need it to check for either BL or Blended, based on the [Modality(s)]@row. If it is BL (as it is in the example), it should look for BL or Blended in the master name, or OL or Online, or just OG.

Same for number of weeks. [Modality(s)]@row has either 6w or 10w, or some number + "w". The master names, once they are all standardized will have this, but many of the current master names may have 6w or 6wk. So if it is looking for 6w, it wouldn't match with a master name with 6wk.

So, while there are FOUR criteria (not just 3, as in your AWESOME formula), two of the criteria have special cases, not always exact matches to what is in the Modality(s) field.

Any thoughts on best way to approach this?

 

Paul Newcome

To add additional sets of criteria, just continue the pattern (see underlined portion below).

 

JOIN(COLLECT({Master Sheet Course Name}, {Master Sheet Course Name}, FIND([criteria 1], @cell) > 0, {Master Sheet Course Name}, FIND([criteria 2], @cell) > 0, {Master Sheet Course Name}, FIND([criteria 3], @cell) > 0, {Master Sheet Course Name}, FIND([criteria 4], @cell) > 0))

.

To address the issues with Criteria 3 and 4...

.

3. To use an OR function in this, you would use it in the same manner as in a COUNTIFS or SUMIFS function. Set your range, then use the OR in the criteria portion.

....................{Master Sheet Course Name}, OR(FIND([criteria 3.a], @cell) > 0, FIND([criteria 3.b], @cell) > 0), ................

.

4. If it will always be weeks, look for the number and forget about the difference between w, W, + W, wk, etc... To keep the FIND function from picking up on a 6 or 10 within the course code, Use the third [start_position] portion of the FIND function.

I personally feel it may be easier to break this down into helper columns anyway having one for each of the 4 criteria. Then reference the helper columns in your search formula.

By doing this, you can also use a LEN/JOIN to count how many characters are in the Course Subject and Code portions and add 1, then take that number and put it in place of the third portion of the FIND function to start looking at numbers AFTER the Course Code.

 

This would look something like (assuming you have it broken down into the helper columns for each set of criteria)...

 

=FIND([criteria 4], @cell, LEN(JOIN([Criteria 1 Helper]@row:[Criteria 2 Helper])) + 2)

 

(I used +2 to create a little buffer for any spaces and/or dashes that may be in there. You can use whatever number you'd like)

.

So the basic (haha) breakdown I am suggesting would be the following:

 

[Criteria 1 Helper]: Formula in your above post.

[Criteria 2 Helper]: Formula in your above post.

[Criteria 3 Helper]: Formula in your above post.

[Criteria 3 Helper Helper]: =IF([Criteria 3 Helper]@row = "OL", "online")

(this will establish the second part for the OR function. If the modality is "online", your formula will pull "on", so the only additional options for that particular modality would be "OL", so if we are looking for both options for online, then you will be set. nothing added for blended because it starts with "bl" which means if we search for "bl" (which is what your RIGHT function will pull) it will pick up both. OG doesn't have an "or" to it, so we can ignore that too.)

[Criteria 4 Helper]: Adjust your formula above to only pull the number. You can either use a MID function or your current RIGHT function with a SUBSTITUTE function to replace and text (as opposed to numbers) with a blank "". I suggest a MID function.

.

Once you get all of that in place, you can use something along the lines of...

 

IF(ISTEXT(JOIN(COLLECT({Master Sheet Course Name}, {Master Sheet Course Name}, FIND([Criteria 1 Helper]@row, @cell) > 0, {Master Sheet Course Name}, FIND([Criteria 2 Helper]@row, @cell) > 0, {Master Sheet Course Name}, OR(FIND([Criteria 3 Helper]@row, @cell) > 0, IF(ISTEXT([Criteria 3 Helper Helper]@row), FIND([Criteria 3 Helper Helper]@row, @cell)) > 0), {Master Sheet Course Name}, FIND([Criteria 4 Helper]@row, @cell, LEN(JOIN([Criteria 1 Helper]@row:[Criteria 2 Helper]@row)) + 2) > 0))), JOIN(COLLECT({Master Sheet Course Name}, {Master Sheet Course Name}, FIND([Criteria 1 Helper]@row, @cell) > 0, {Master Sheet Course Name}, FIND([Criteria 2 Helper]@row, @cell) > 0, {Master Sheet Course Name}, OR(FIND([Criteria 3 Helper]@row, @cell) > 0, IF(ISTEXT([Criteria 3 Helper Helper]@row), FIND([Criteria 3 Helper Helper]@row, @cell)) > 0), {Master Sheet Course Name}, FIND([Criteria 4 Helper]@row, @cell, LEN(JOIN([Criteria 1 Helper]@row:[Criteria 2 Helper]@row)) + 2) > 0)), "No Master Found")

.

This will do what my original formula did. I just added in cell references for helper columns, the OR for the 3rd criteria, and finally the 4th criteria of the weeks looking for just the number starting AFTER the Course Code.

Art Schneiderheinze

That worked!!!!!

So now my sheet pulls the correct master name from the other sheet, unless it doesn't exist and it puts "No Master Found".

Only thing---in the first sheet, the master name is actually a hyperlink that takes the user to the actualy course site in our learning management system. 

In the new sheet, using your formula, the correct master name appears, but it isn't a hyperlink. Now that I have the correct master name, how can I now make that the hyperlink that was in the original sheet? 

 

ORIGINAL (sample with hyperlink, that for this sample does not link to anything)

BUS 453 Blended 6Wk

NEW SHEET (with your formula)

BUS 453 Blended 6Wk (no hyperlink)

 

My first thought was using INDEX-MATCH to find that master name now in the Original sheet, and put the value of the matching cell in my New Sheet (in a new column). Then, I just show that new column, and hide all of the other columns, even the one showing the master name (without the hyperlink).

I tried 

=INDEX({CMR-Courses}, MATCH(Master@row, {CMR-Courses}, 0), 1)

{CMR-Courses} is {Master Sheet Course Name} used in your formula and Master@row is the column that has the correct master name (result of your formula). I find the match, but its not a hyperlink, its just the text.

Any thoughts?

Art Schneiderheinze

That worked!!!!!

So now my sheet pulls the correct master name from the other sheet, unless it doesn't exist and it puts "No Master Found".

Only thing---in the first sheet, the master name is actually a hyperlink that takes the user to the actualy course site in our learning management system. 

In the new sheet, using your formula, the correct master name appears, but it isn't a hyperlink. Now that I have the correct master name, how can I now make that the hyperlink that was in the original sheet? 

 

ORIGINAL (sample with hyperlink, that for this sample does not link to anything)

BUS 453 Blended 6Wk

NEW SHEET (with your formula)

BUS 453 Blended 6Wk (no hyperlink)

 

My first thought was using INDEX-MATCH to find that master name now in the Original sheet, and put the value of the matching cell in my New Sheet (in a new column). Then, I just show that new column, and hide all of the other columns, even the one showing the master name (without the hyperlink).

I tried 

=INDEX({CMR-Courses}, MATCH(Master@row, {CMR-Courses}, 0), 1)

{CMR-Courses} is {Master Sheet Course Name} used in your formula and Master@row is the column that has the correct master name (result of your formula). I find the match, but its not a hyperlink, its just the text.

Any thoughts?

Paul Newcome

Unfortunately I haven't been able to find a way to pull a hyperlink from one sheet to another other than with cell linking. X-sheet references can't pull them. It would have to be done manually. If there is a generic way to use the course name to find the proper section from the home page, you may be able to provide instruction on how to navigate to the appropriate page. Such as... All BUS course codes would be following the same path, from there the number would specify which way to go, so on and so forth. Using a table and a few more formulas could possibly take this one step further and automatically display the correct path based on the course name if there are consistencies like that.

eric.o

Hello,

 

Paul is correct,  currently, we don’t have a method to include the hyperlink when referencing a value from another sheet utilizing a formula but this will be considered as a possibility for future development.

 

Cheers, 

Eric  

Smartsheet Support