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 :-)
Bookmarks