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.
