Multiple criteria formula help

[richleeb2]

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

[Luke_C · Accepted solution]

Hi @richleeb2 

Here's one way:

1. Transpose the data
2. IF statement for each piece of logic
3. Concatenate results and join back

[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. 

[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!