Formula question involving if-contains-then I'm struggling with

[adriennelenker]

Hi all,

This is an issue I'm struggling with. I I have the table below with the following conditions needed. If a condition is true, create a new column that's filled in for all of the User ID rows.

A - If a User ID contains any month before 2021, populate ResultA for all User ID rows.

B - If a User ID contains "deactivated", populate ResultB for all User ID rows

C - Populate ResultC with the person's name of the latest month, the same name for all ResultC rows for a single UserID 

D - For a User ID, for Condition D, if the row of the latest month is null, populate ResultD for all User ID rows.

The desired result is below.

Condition A	User ID	Condition B	Condition C	Condition D	ResultA	ResultB	ResultC	ResultD
Dec 2020	A	Active	James	1	x	x	Matt	x
Jan 2021	A	Active	Paul	2	x	x	Matt	x
Feb 2021	A	Deactivated	Matt		x	x	Matt	x
Jan 2021	B	Active	Ankur	1			Sanjay	x
Feb 2021	B	Active	Sanjay				Sanjay	x
Jan 2021	C	Active	Jessica	1		x	Brooke
Jun 2021	C	Deactivated	Brooke	2		x	Brooke

I am trying to use formulas with if-contains and end up with the following:

Condition A	User ID	Condition B	Condition C	Condition D	ResultA	ResultB	ResultC	ResultD
Dec 2020	A	Active	James	1	x
Jan 2021	A	Active	Paul	2
Feb 2021	A	Deactivated	Matt			x	Matt	x
Jan 2021	B	Active	Ankur	1
Feb 2021	B	Active	Sanjay				Sanjay	x
Jan 2021	C	Active	Jessica	1
Jun 2021	C	Deactivated	Brooke	2		x	Brooke

Is there a way to write the formulas to obtain my desired result? I appreciate all help. Thank you.

[ShankerV]

Hi @adriennelenker 

Please find the expected output.

Many thanks

Shanker V

[adriennelenker]

Hi Shankar,

I can't take a detailed look right now, but is this the top table or the bottom table I posted? From the screenshot it looks like the bottom table, I'm trying to obtain the top table. Thanks

[ShankerV]

Hi @adriennelenker 

Got it, it is possible; with few tweaks in the workflow.

[ShankerV]

Hi @adriennelenker 

Please find the expected result as per the first table.

Many thanks

Shanker V

[adriennelenker]

Hi Shankar,

Thank you for replying, this is very helpful. i’m just wondering, is there any way to make this dynamic so it works with different situations, not just this one? I have a large dataset with differing values in the columns. Also, is this only possible with multi-row formulas, and not with regular formulas? Thanks

[adriennelenker]

Also, is condition C working? Thanks

[ShankerV]

Hi @adriennelenker 

The workflow created is dynamic.

It cant be achieved with regular formula's as we need to check the previous row data's. That is possible only with Multi-row formula tool.

[adriennelenker]

Thank you, it’s a bit late where I am, I will definitely check in the morning. I appreciate the help.

[grazitti_sapna · Accepted solution]

@adriennelenker , Please find my below solution if this was what you were looking for: