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.