Multiple criteria formula help

Hi,

I'm trying to use the formula tool to add another column with output dependent on certain rules below and concatenate the result in the output column shown

Rule 1: None of 4 columns can be blank

Rule 2: None of 4 columns can have the word "grape"

Rule 3: Col 2 must be a whole number

Col 1	Col 2	Col 3	Col 4	Output
pear	4		banana	Col 3 cannot be blank
apple	3.567	apple	grape	Col 2 must be whole number, Col 4 cannot be grape
strawberry	kiwi	grape		Col 2 must be whole number, Col 3 cannot be grape, Col 4 cannot be blank

Help

Accepted answers

Luke_C

Hi @richleeb2

Here's one way:

Transpose the data
IF statement for each piece of logic
Concatenate results and join back

New Workflow2.yxmd

All comments

Luke_C

Hi @richleeb2

Here's one way:

Transpose the data
IF statement for each piece of logic
Concatenate results and join back

New Workflow2.yxmd

richleeb2

Thank you! How do I add a space after each comma?

phottovy

I had a very similar approach to @Luke_C . I always recommend pivoting your data instead of writing formulas across multiple columns.

Multiple Column Formulas.yxmd

Luke_C

@richleeb2

Add the space in the separator in the summarize tool:

richleeb2

Thank you Luke, this worked perfect but I want to add one more criteria to Col 2. In addition to it must be a whole number, it cannot be blank. How do I update the formula?

IF IsEmpty([Value])
Then [Name] + ' cannot be blank'
Elseif Contains([Value],'N/A')
Then [Name] + ' cannot be N/A'
Elseif !IsInteger([Value]) and [Name] = 'Rehosting Question 2'
Then [Name] + ' must be a whole number'
else null()
endif

Luke_C

Hi @richleeb2

If column two is left blank it should already be handled by the logic because it is checking for all fields being blank. Please provide more information if that's not working correctly with your data. Otherwise be sure to accept the solution.

richleeb2

Sorry I meant 0 but I figured it out!

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167