Assistance Appreciated

Melanie Sanders

Hello,

This one is a bit tricky. I need to create a formula that will look at 4 columns in a row. If text is in any column in that row, based on the column the text is in will need to be text added to the 5th column. The words to enter into the LICENSE CATEGORY FORMULA column will be one of these 4 words: PHARMACY, DISTRIBUTOR, MANUFACTURING or OTHER based off which column has text in it.

Example in the first row: there is text in the LICENSE TYPE - MANUFACTURING column so the LICENSE CATEGORY FORMULA column should display MANUFACTURING in that cell. It does not matter what the text is in the license type columns only that there is text.

The same will be true for second row and the LICENSE CATEGORY FORMULA column should display MANUFACTURING.

The third row there is text in the LICENSE TYPE - DISTRIBUTOR column so the LICENSE CATEGORY FORMULA column should display DISTRIBUTOR in that cell.

Please let me know if you have any ideas on how best to accomplish this or if I should add additional columns to accomplish this formula.

Thank you!

Adam Murphy

Hi @Melanie Sanders,

You should be able to accomplish this with a simple nested IF statement, something like the following:

=IF([License Type - Pharmacy]@row <> "", "Pharmacy", IF([License Type - Distributor]@row <> "", "Distributor", IF([License Type - Manufacturing]@row <> "", "Manufacturing", IF([License Type - Other]@row <> "", "Other", ""))))

Hope that helps!

Adam Murphy

Hi @Melanie Sanders,

You should be able to accomplish this with a simple nested IF statement, something like the following:

=IF([License Type - Pharmacy]@row <> "", "Pharmacy", IF([License Type - Distributor]@row <> "", "Distributor", IF([License Type - Manufacturing]@row <> "", "Manufacturing", IF([License Type - Other]@row <> "", "Other", ""))))

Hope that helps!

Melanie Sanders

@Adam Murphy That ws it! Thank you so much!

Melanie Sanders

@Adam Murphy I do have a reverse scenario though. I have a column titled LICENSE TYPE that I would like to populate if there is text in one of the above 4 columns with the text in that column. Each row will only have 1 of the 4 columns populated. Can you assist with this formula as well? In rows 1-3 the LICENSE TYPE column should contain Pharmacy-nonresident and rows 4-5 would have Sterile Compounding while 6-10 would have Manufacturer, etc.

Thank you!

Adam Murphy

Hi @Melanie Sanders,

Try something like this: =JOIN(COLLECT([LICENSE TYPE - PHARMACY]@row :[LICENSE TYPE - OTHER]@row , [LICENSE TYPE - PHARMACY]@row :[LICENSE TYPE - OTHER]@row , NOT(ISBLANK(@cell ))), CHAR(10))

Hope that helps!