SOLVED
Home

Count if help

%3CLINGO-SUB%20id%3D%22lingo-sub-335653%22%20slang%3D%22en-US%22%3ECount%20if%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-335653%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20company%20had%20a%20system%20outage%20where%20everyone%20lost%20access%20to%20different%20roles%20and%20then%20gained%20them%20back.%20I%20need%20to%20make%20sure%20everyone%20had%20all%20the%20appropriate%20roles%20restored%2C%20no%20more%20or%20less.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20A%20column%20are%20everyone's%20usernames.%3C%2FP%3E%3CP%3EIn%20the%20B%20column%20are%20the%20actions%20per%20user%2C%20either%20%22Profile%20deleted%22%20or%20%22Profile%20added%22.%3C%2FP%3E%3CP%3EIn%20the%20C%20column%20are%20which%20profile%20was%20added%2Fdeleted%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20make%20sure%20that%20for%20each%20user%2C%20the%20exact%20profiles%20that%20were%20deleted%20matches%20the%20exact%20profiles%20that%20were%20added.%20I've%20attached%20a%20sample%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%3F%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-335653%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338351%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20if%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338351%22%20slang%3D%22en-US%22%3E%3CP%3EOr%20you%20may%20apply%20conditional%20formatting%20rule%20with%20approximately%20the%20same%20formula%3C%2FP%3E%0A%3CPRE%3E%3DNOT((SUMPRODUCT((%24A%242%3A%24A%2419%3D%24A2)*(%24C%242%3A%24C%2419%3D%24C2)*(%24C%242%3A%24C%2419%26lt%3B%26gt%3B%22%22))%3DSUMPRODUCT((%24A%242%3A%24A%2419%3D%24A2)*(%24D%242%3A%24D%2419%3D%24C2)*(%24D%242%3A%24D%2419%26lt%3B%26gt%3B%22%22)))*%0A(SUMPRODUCT((%24A%242%3A%24A%2419%3D%24A2)*(%24C%242%3A%24C%2419%3D%24D2)*(%24C%242%3A%24C%2419%26lt%3B%26gt%3B%22%22))%3DSUMPRODUCT((%24A%242%3A%24A%2419%3D%24A2)*(%24D%242%3A%24D%2419%3D%24D2)*(%24D%242%3A%24D%2419%26lt%3B%26gt%3B%22%22))))%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20420px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F74801i53BF5FE68792A413%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338311%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20if%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338311%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20can%20add%20the%20labels%20%22Deleted%22%20and%20%22Added%22%20in%20Cells%20E1%20and%20F1%2C%20respectively.%20In%20Cell%20E2%2C%20we%20enter%20the%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(OR(ISBLANK(C2)%2C%3CBR%20%2F%3ECOUNTIFS(%24A%242%3A%24A%2419%2C%24A2%2C%24D%242%3A%24D%2419%2CC2))%2C%22%22%2C%3CBR%20%2F%3EC2)%2C%20copied%20down%20to%20Cell%20E19.%20In%20Cell%20F2%2C%20we%20enter%20the%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(OR(ISBLANK(D2)%2C%3CBR%20%2F%3ECOUNTIFS(%24A%242%3A%24A%2419%2C%24A2%2C%24C%242%3A%24C%2419%2CD2))%2C%22%22%2C%3CBR%20%2F%3ED2)%2C%20copied%20down%20to%20Cell%20F19.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formulas%20in%20Columns%20E%20and%20F%20return%20the%20letter%20of%20the%20Deleted%20and%20Added%20profiles%2C%20respectively.%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

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

2 Replies
Solution

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<>""))))

image.png

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies