Forum Discussion
Count if help
My company had a system outage where everyone lost access to different roles and then gained them back. I need to make sure everyone had all the appropriate roles restored, no more or less.
In the A column are everyone's usernames.
In the B column are the actions per user, either "Profile deleted" or "Profile added".
In the C column are which profile was added/deleted
I need to make sure that for each user, the exact profiles that were deleted matches the exact profiles that were added. I've attached a sample file
Any help? Thanks
We can add the labels "Deleted" and "Added" in Cells E1 and F1, respectively. In Cell E2, we enter the formula:
=IF(OR(ISBLANK(C2),
COUNTIFS($A$2:$A$19,$A2,$D$2:$D$19,C2)),"",
C2), copied down to Cell E19. In Cell F2, we enter the formula:=IF(OR(ISBLANK(D2),
COUNTIFS($A$2:$A$19,$A2,$C$2:$C$19,D2)),"",
D2), copied down to Cell F19.The formulas in Columns E and F return the letter of the Deleted and Added profiles, respectively.
Please see attached file.
Or you may apply conditional formatting rule with approximately the same formula
=NOT((SUMPRODUCT(($A$2:$A$19=$A2)*($C$2:$C$19=$C2)*($C$2:$C$19<>""))=SUMPRODUCT(($A$2:$A$19=$A2)*($D$2:$D$19=$C2)*($D$2:$D$19<>"")))* (SUMPRODUCT(($A$2:$A$19=$A2)*($C$2:$C$19=$D2)*($C$2:$C$19<>""))=SUMPRODUCT(($A$2:$A$19=$A2)*($D$2:$D$19=$D2)*($D$2:$D$19<>""))))
We can add the labels "Deleted" and "Added" in Cells E1 and F1, respectively. In Cell E2, we enter the formula:
=IF(OR(ISBLANK(C2),
COUNTIFS($A$2:$A$19,$A2,$D$2:$D$19,C2)),"",
C2), copied down to Cell E19. In Cell F2, we enter the formula:=IF(OR(ISBLANK(D2),
COUNTIFS($A$2:$A$19,$A2,$C$2:$C$19,D2)),"",
D2), copied down to Cell F19.The formulas in Columns E and F return the letter of the Deleted and Added profiles, respectively.
Please see attached file.
