If - Help

B_Y_ID8

Hi all,

I am trying to introduce another part to my formula (enclosed as New Workflow 1) whereby:

if IsNull(Cat1) and IsNull(Cat2) Then

Check Category against Other1

Check Category against Other2

Check Category against Other3

Check Category against Other4

If Match Then Yes

Else No

For example on screenshot provided(Image): I would expect Row 8, 10, 13 and 14 to come back as Yes. Row 4 seems to be correct as doesnt match.

Any help please. I have been struggling to get the next part of the IF to be added to my existing IF statement.

Thanks

New Workflow1.yxmd

Image.png

Developer

Accepted answers

Maskell_Rascal

@B_Y_ID8

This should work for you.

IF IsNull([Category])
	THEN 'No'
ELSEIF [Category] IN ([Cat1],[Cat2])
	THEN 'Yes'
ELSEIF 'DD' IN ([Cat1],[Cat2]) AND [Category]!='DD'
	THEN 'DD Error'
ELSEIF IsNull([Cat1]) AND IsNull([Cat2]) AND [Category] IN ([Other1],[Other2],[Other3],[Other4])
	THEN 'Yes'
ELSE 'No'
ENDIF

I modified the formula to only look if for Cat1 and Cat2 are null and after all the other operations first run.

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

atcodedog05

Hi @B_Y_ID8

Modify the elseif like below. I am changing the order.

IF IsNull([Category])
	THEN 'No'
ELSEIF 'DD' IN ([Cat1],[Cat2]) AND [Category]!='DD'
	THEN 'DD Error'
ELSEIF [Category] IN ([Cat1],[Cat2])
	THEN 'Yes'
ELSEIF IsNull([Cat1]) AND IsNull([Cat2]) AND [Category] IN ([Other1],[Other2],[Other3],[Other4])
	THEN 'Yes'
ELSE 'No'
ENDIF

Please check and let me know. Priority is changed when order is changed it first come first serve

In the previous IF

ELSEIF [Category] IN ([Cat1],[Cat2])
THEN 'Yes'

was checked first in your second case it was true hence it didnt check for DD error

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

B_Y_ID8

Hi atcodedog05

Apologies for not posting the response. I have accepted both as solutions however in the end the chosen logic was what Maskell-Rascal wrote.

Many thanks

atcodedog05

Hi @B_Y_ID8

Is this the expected output

Please revert back quickly i might be going offline in sometime

ABC.yxmd

Maskell_Rascal

@B_Y_ID8

Change the last Null() to [Column A] and you'll be covered.

IF IsNull([Column A]) and [Column C]=1 THEN 
IF ToString([Column B]) in ('3','7','12') THEN 1 
ELSEIF ToString([Column B]) in ('16','18','19') Then 2
ELSEIf ToString([Column B]) in ('24','27','32') Then 3
ELSE Null() ENDIF
ELSE [Column A] ENDIF

Thanks!

Phil

B_Y_ID8

Hey atcodedog05,

Hoping that you can help again - this is the formula that I have written - however just not sure how to introduce the final bit (mine is a bit of a cheat at the moment whereby anything >1 will output as 125*52/12.

column c (being the new column from the formula)

If isnull([column a]) and isnull([column b]) then 0

Elseif ([column a]>1) then ((125*52)/12)

Elseif isnull([column a]) then

If ([column b]>1) then ((125*52)/12)

Else ((100*52)/12) endif

Whereas I would like it to be:

if column a and column b are both null then 0

if column a = 1 then 100*52/12 or column a = 2 then 125*52/12 else null

if column a is null then if column b = 1 then 100*52/12 or column b = 2 then 125*52/12 else null

i have enclosed the sample file with column c being the expected outcome from the formula.

thanks

SAMPLE.xlsx

All comments

Maskell_Rascal

Hi @B_Y_ID8

Try this formula:

IF IsNull([Category])
	THEN 'No'
ELSEIF [Category] IN ([Cat1],[Cat2],[Other1],[Other2],[Other3],[Other4])
	THEN 'Yes'
ELSEIF ([Cat1]IN('DD') AND [Category]!='DD') || ([Cat2]IN('DD') AND [Category]!='DD')
	THEN 'DD Error'
ELSE 'No'
ENDIF

This lets the Category field find any match across the columns specified.

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

B_Y_ID8

Hi Phil,

Thanks for the response - it nearly does - apologies but I probably missed an extra bit out.

If content is already in Cat1 or Cat2 then it should not check Other1, Other2, Other3 and Other4. It should determine if Category matches either Cat1 or Cat2 and output Match Yes/No/DD Error.

Only if Cat1 and Cat2 are empty then the check of Category against Other1, Other2, Other3 and Other4 should take place and then output Match Yes/No.

That is the reason why I put: If isnull(cat1) and isnull(cat2) then check. (but I wasnt clear enough to begin with - my fault)

Hope this makes sense.

Thanks

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703