Create Table of Results Based on Matching Portion of Characters in Field (VLOOKUP or INDEX maybe?)

billcasemich

New to Smartsheet...

Scenario: I have a list of treatments to apply based on various observations made. When I make an observation, various columns concatenate to give me an ObservationCode. Once the ObservationCode is generated, I want to interrogate the list of treatments and return all potential relevant treatments.

Example: ObservationCode is 1X2X3X4A5D6X7X. If it matters, the nomenclature is that items 1-3 and 6 and 7 are irrelevant to the particular observation, so it assigns X (if 1 is "flavor" and the observation is of a chair, it is irrelevant). In this example, items 4 and 5 are relevant to a handful of treatments.

My thought is to have my list of treatments in Column 1, and the relevant observation codes in Column 2. So for Treatment1, relevant observations are 4A and 5D. BOTH must be met to return Treatment1. Simply put:

Column1 Column2

Treatment1 4A 5D [Note: not sure if I would want a space, comma, or something else in Column2]

What is the proper formula to do the following:

1. Evaluate the ObservationCode
2. Evaluate the Treatment List and look for matches in Column2, above. A treatment might have 1 or more matching Observation Code required
3. If there is a full match, return the value listed in Column1, above.

To complicate matters, a single ObservationCode might return multiple Treatments

Thank you for your time and consideration; I'm happy to do the research, I'm just not sure where to start.

Scott Orsey

Hi @billcasemich , That's great you found a solution! I must admit, I don't completely understand how this works, and I'm especially baffled by the IF/AND/FIND function you've written which seems to return a {range}. I don't know how that would return the correct Treatment # from {Treatments Range 2}. There doesn't appear to be anything like INDEX() that would indicate the correct value to return.

Regardless, if you now have a working sheet with all of the Objects in rows and several columns populated with the correct Treatments (or blanks) it looks like you are most of the way there.

To pick the treatments, perhaps all you need to do is put a checkbox column next to each of the Match columns. Then you could concatenate all of the selected treatments into a single column so that you can report those out somewhere for each object.

Great job and I wish you continued success. Be well!

Scott Orsey

Hi @billcasemich , Despite having a number of text functions, SS isn't particularly strong at parsing strings like yours. I would probably tackle this with helper columns that look at each two character string independently and decides what to do. Something like:

=IF(MID([Column2]@row, 2, 1) = "X", "", MID([Column2]@row, 1, 2))

In this, you increment the bolded numbers by two in each helper column... so the 2 becomes 4, 6, 8, etc and the 1 becomes 3,5,7, etc. Then you can concatenate all of these columns to get your value for Column 2.

For question 2, I'm not sure what the data might look like that you are looking up. I would consider using CONTAINS().

For question 3: it may be easier to do this comparison off of the helper columns that I suggested above. I think you will need to parse things out, so why not just start with them parsed?

Does this help? Be well!

billcasemich

Hi @Scott Orsey, thanks for the quick response!

This is helpful, inasmuch as I can see how you use the MID function to evaluate the value. As you say, I can then look for particular values in particular locations, though I do indeed have the individual parts (the "1X" and "2X" and so-on in separate columns already). So let's set the idea of parsing a field to one side, and say I just have 7 columns, and each of them has a value such as 1X, 2X, etc.

Effectively, what I'm trying to say is "ok, for a Treatment to apply, it must meet certain criteria, and I'm going to evaluate all of those criteria and see if there is a match. So in terms of data, if I had the following table (I'm going to put spaces between values, but if they needed to be commas or something else, that can be done):

Criteria Treatment

1A 4F 6B Treatment1

2B 5C Treatment 2

7A Treatment 3

By making observations, I end up with the seven columns which describe various criteria:

Observation Color Shape Weight Material Density Texture Temperature

1 1A 2X 3B 4C 5X 6A 7A

2 1B 2B 3X 4X 5C 6C 7B

3 1A 2X 3C 4F 5C 6B 7A

What I'm trying to accomplish is that for Observation 1, because it includes 7A, it would return Treatment 3. For Observation 2, because it includes 2B and 5C, it would return Treatment 2. For Observation 3, because it includes 1A, 4F, 6B it would return Treatment 1, and perhaps on the next row, it would ALSO return Treatment 3, because it includes 7A.

I can describe which criteria define all of the Treatments (there are only about 50 treatments), and I might have dozens or hundreds of observations. So I want to take the observations and produce a table of treatments. Assuredly I will get duplicate treatments (Both Observation 1 and 3 returned Treatment 3), but I figure I can filter those out.

Thanks for your help - I hope I'm making sense.

Scott Orsey

Hi @billcasemich ,This is quite the puzzle. While it would be fairly straightforward to write out the algorithm in a programmed routine, this doesn't lend itself easily to a spreadsheet solution like SS. It might be easier to extract the data from SS, do some processing on it in another tool, then bring it back in. Then again, we might as well noodle through it a bit in SS if we can. I have a few more questions:

1) Do you think you will ever want more than 7 traits observed? If so, I think that will make this undoable in SS. However, 7 might work. As you've found, "coding" your observations on the front end is fairly straightforward. However, identifying which observations match which treatments is much tougher. The only approach I can think of to do this would be brute force (which would be easier to program). For each observation, you have 2^N - 1 (where N is the number of traits observed) possible combinations which could match a treatment. In your original example (1X2X3X4A5D6X7X) you have 2 traits observed, and therefore 3 possible combinations that could match a treatment (4A, 4A5D and 5D). If you had three traits you'd have 7 possibilities (100, 110, 111, 101, 010, 011, 001) and so on. If 7 possible traits are to be considered, then you'd have 2^7 - 1 = 127 combinations. It's likely that your solution will need 2 or even 3 columns for each possibility, so 7 traits is a practical max since SS limits us to 400 columns..

2) Do you ever allow an "unobserved" trait to be in the treatment when it wasn't in the observation? For example, if you had Treatment 4 = 2C 7A, could you match that to observations 1 or 3 (which have 2X which I assume means that nothing was observed)? If so, then I think you may also quickly be beyond the SS limits.

3) Finally, how do you prioritize treatments? Are there other parameters other than matching the observation? What if no combination of treatments covers all of the observed traits? Or, do you just want to know every treatment that could apply and then a human will sort out which ones to go with?

Depending on the answers to these, I may have some ideas about where to go.

Be well

billcasemich

Thank you @Scott Orsey!

I've made significant progress on matching the observations to the treatments. I'm providing a lot of information in the interest of helping others if they run across such a thing in the future.

As you suggested, the key for this part is a helper sheet. I have a helper sheet called "Helper Sheet" with "Object ID" in column 1, then 7 columns to the right, one for each individual observation about the object. The collection of individual observations functions similar to a Vehicle Identification Number, where the contents of a specific column has specific meaning.

In each of the individual observation columns, I have a VLOOKUP back to the observations sheet, and using the full range of the observations sheet as the lookup table, and incrementing the column number each time. Example:

=IFERROR(VLOOKUP($[Object ID]@row, {Observations Range 1}, 2, false), "")

Pause to say that I have a separate "Treatments" sheet with two columns. The first column includes matching observations (i.e. 4A, 5D), and the second column has the name of the treatment (i.e Treatment 1, etc.).

To the right of the seven individual observation columns, I have seven "match" columns labeled "Match1", "Match2", and so on. These columns use IF/AND/FIND to evaluate whether or not an observation code exists in a specific observation column (or more than one), and if so, it evaluates the "Treatments" sheet and returns the matching treatment. Example:

=IF(AND(FIND("4A", Volume@row) > 0, FIND("5D", Zone@row) > 0), {Treatments Range 2}, "")

So on my sheet, if the "Volume" row reads "4A" AND the "Zone" row reads "5D", the "Match1" column shows "Treatment 1", or else it's blank.

The result is that if the individual observation codes in the individual columns match a treatment, I end up with a row something like:

ObjectID | Match 1 | Match 2 | Match 3

6 | Treatment 1 | <blank> | Treatment 3

So now my remaining challenge is this!

I want to provide yet another sheet or form or something where someone is presented with the Object ID and a list of choices for Treatments for each object. They can select 1 or more. I was thinking I would like a list, but as I type this, maybe I can somehow utilize the helper sheet and let them select from there? There shouldn't be more than 3 or 4 potential treatments for any one object.

Let me know what you think, and thanks again for the help!

Scott Orsey

Hi @billcasemich , That's great you found a solution! I must admit, I don't completely understand how this works, and I'm especially baffled by the IF/AND/FIND function you've written which seems to return a {range}. I don't know how that would return the correct Treatment # from {Treatments Range 2}. There doesn't appear to be anything like INDEX() that would indicate the correct value to return.

Regardless, if you now have a working sheet with all of the Objects in rows and several columns populated with the correct Treatments (or blanks) it looks like you are most of the way there.

To pick the treatments, perhaps all you need to do is put a checkbox column next to each of the Match columns. Then you could concatenate all of the selected treatments into a single column so that you can report those out somewhere for each object.

Great job and I wish you continued success. Be well!

billcasemich

Likewise @Scott Orsey, all the best!

I might have confused things in my explanation on the IF/AND/FIND. In that case, the {Treatments Range 2} actually refers to a single cell (so maybe I could just point to that cell?) with the name of the Treatment, since the treatments are all that unique.

I think the checkbox is a good idea, and might present opportunities for automation. I'll keep going, but I have what I need for now. Thanks again!