Looking up another table in another worktab and stringing

[YMUNSHI]

Hi everyone,

Thank you once again for viewing this.

I wanted to ask if you could help me on three formulas please. I have attached a spreadsheet showing the data.

1) Modification indicator

In the spreadsheet in the worktab 'Modifications', there is a list of all the phrases an item may come as, such as breath-actuated, controlled-release, etc.

In the 'Main' worktab, in column F, I need a formula where if any item has any phrase corresponding to any of the phrases in the 'Modifications' tab, a hash ('#') is automatically input into that column.

So generally, if any phrase in column B is equal to any phrase in worktab 'Modifications', cells A4:[forever], then the hash symbol should be input in column F for that corresponding item.

For example, in cell B2, it says Chewable. This is a modification, as per the 'Modifications' tab. Therefore, in cell F2, a '#' should be automatically input.


2) Form

This might be a little complicating, but I'll try my best to explain.

If any form mentioned in column C corresponds with any form (must be exactly matching) in worktab 'Forms', then its corresponding initial at the top of each list (row 2) must be input into column G.

So for example, in cell C751, it says 'Gel'. This is mentioned in the 'Forms' tab, so its initial is situated in row 2, specifically D2 as 'G'. In cell G751, a 'G' should be automatically keyed. This holds true even if any descriptives are mentioned along with the form, for example, for cell C328 where it says 'Dry powder inhaler, we would count the 'inhaler' bit as the form and ignore the 'dry powder' bit.

However, there may be items with two totally different forms quoted in the cell. So for example, C668 has 'Liquid eye drop gel'. Because this is not mentioned anywhere quote-for-quote in the 'Forms' list, it therefore has three separate forms: liquid, eye drop, and gel, all three of which are mentioned separately in their respective groups. In cases such as these, cell G668 should have a 'Z' automatically keyed whenever there are more than one listed forms in a cell. The same holds true for items such as 'Solution for infusion vials'. 'Solution for infusion' is not listed under group 'J', so we treat this form as two separate forms: 'solution' and 'vials', both of which fall under separate groups. Therefore, this needs to be treated as a 'Z'. This is not true for quote-for-quote forms, such as C467 'Suspension for injection' which appear to have two different forms, because this is a quote-for-quote match in the 'Forms' worktab, so we would still treat this as a 'J' group form.

Similarly, there may be forms which are not listed anywhere in the 'Forms' worktab. In cases such as these, if any form mentioned in column C does not match any item in the 'Forms' tab, column G should have 'Z' automatically input. This follows on what was said in the previous paragraph. So for example, cell C882 says 'Transdermal Patch' which is not a listed form, so a 'Z' should be keyed as its form in cell G882.

There may be more than one form quoted in column C which belong to the same list in 'Forms'. So for example, C726 'Solution for injection vials' has two forms belonging to the same group, and they are 'Solution for injection' and 'vials', both of which fall under group 'J'. In this case, cell G726 should still have 'J' automatically keyed.


3) '-Free' indicator

This one should be reasonably straightforward. If any text is entered in column D, the corresponding cell in column H will have '\' keyed automatically. So for example, because cell D314 is populated with text, cell H314 will have '\' automatically keyed.



I have done populated columns F-H to reflect how the results should ideally appear.

I don't think there will be any more questions/queries after this as the forum has helped me out so much in getting this, and it will definitely help me learn how these formulas work.

Thank you once again :-)

[mcmahobt]

In F2 try this:

Please Login or Register  to view this content.

and drag down.

In H2 try this:

Please Login or Register  to view this content.

and drag down.

As for G2, I understand what you're trying to accomplish, but with your current setup it is probably better to go into VBA to achieve the results you're looking for. However, if you were willing to categorize your lists alphabetically (almost like you already have), it may be possible to do.

[Teblol]

I have attached the workbook that I have partially solved your column "G" formula in. It will find the Indicator on your "Forms" tab and list the header letter. As you can see we have to use helper columns. If you do not want these helper columns to be visible simply hide them. Let me know if this is what you are looking for. If so, I will try to figure out how to do the second part...

"However, there may be items with two totally different forms quoted in the cell. So for example, C668 has 'Liquid eye drop gel'. Because this is not mentioned anywhere quote-for-quote in the 'Forms' list, it therefore has three separate forms: liquid, eye drop, and gel, all three of which are mentioned separately in their respective groups. In cases such as these, cell G668 should have a 'Z' automatically keyed whenever there are more than one listed forms in a cell. The same holds true for items such as 'Solution for infusion vials'. 'Solution for infusion' is not listed under group 'J', so we treat this form as two separate forms: 'solution' and 'vials', both of which fall under separate groups. Therefore, this needs to be treated as a 'Z'. This is not true for quote-for-quote forms, such as C467 'Suspension for injection' which appear to have two different forms, because this is a quote-for-quote match in the 'Forms' worktab, so we would still treat this as a 'J' group form."

Strings.xlsx

[mcmahobt]

Here's a possible solution for all requirements. I strongly advise you don't use nested IF statements in this application...that setup is very volatile and not scalable.

[Teblol]

That is definitely a cleaner way, but I didn't want to change the format of his "Forms" tab. Leaving it the way it is allows him to easily add more info. Question... I am still learning formula's myself and I am not sure what you mean by "scalable" in this situation?

[mcmahobt]

That many nested IF statements can cause a lot of problems if the data ranges are not static. Consider the scenario where OP needs to incorporate a new letter for an item, or if one item becomes obsolete and is no longer needed. OP would need to manually change every nested IF statement to account for these changes, and since the formula you provided changes every row, a simple Find/Replace wouldn't suffice. Sure, you could change the first formula and drag down through the last row of your data, but why do that? Why not utilize one formula that accounts for any change in data?

Yes, the changing of the forms worksheet was necessary. Excel can do a lot, but it does have its limitations. If OP absolutely cannot change the Forms worksheet, I'd recommend the VBA route. Although your solution technically works for this specific scenario, it is volatile in the sense that it is not scalable enough to incorporate changes in data, besides the fact that it is processing intensive.

[Teblol]

Ah, great points. I didn't think about what would happen if he took a letter away or had to add a new one. Thanks for taking your time to reply and explain.

[mcmahobt]

No worries. If it makes you more efficient and helps someone else, everyone wins right?

[YMUNSHI]

Hi everyone,

Just wanted to say thank you so much for replying back and apologies for the late reply, unfortunately, I've been away from the PC.

The formulas you guys have come up with are fantastic and are working fine.

For the 'Forms' bit, there are three problems:

a) Forms that are not listed 'Forms' don't have a 'Z' input;
b) Forms that have an additional description, such as 'Dry powder inhaler' are being missed off - because this mentions 'Inhaler' as a form, it should be keyed as 'I';
c) Items with more than one listed form are not being input with a 'Z' (such as 'liquid eye drop gel' which has three listed forms).

Is there any way this can be rectified or implemented please?

Thank you once again for all your help, you guys have been amazing. I'm still learning how to use complex formulas in Excel, and the help you're giving is certainly playing its part in this :-)

[FlameRetired]

YMUNSHI,

Is leaving the Forms sheet in it's original format a requirement, or are you able to use mcmahobt's modified Forms sheet approach?

If you are able to use mcmahobt's approach this small modification should take care of the exceptions you've listed. If not let us know.

With a small lookup table in Q2:R3 this modified formula in column G

Please Login or Register  to view this content.

It is not array entered. The formulas for columns F & H were already solved above.