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.
Solved! Go to Solution.
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
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
Also, is condition C working? Thanks
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.
Thank you, it’s a bit late where I am, I will definitely check in the morning. I appreciate the help.