CODEWITHSUNDEEP
excelexcel-formula
sr28
sr28

Reputation: 5106

Is there a way to do a count of duplicate rows that match certain criteria?

Here as example of my data:

User    Role
Bob    Admin A
Bob    Admin B
Bob    Admin C
Bob    Admin D
Frank    Admin A
Frank    Admin C
Frank    Admin D


Is there a way to count the users where they have both Admin A and Admin B roles OR Admin C and Admin D roles. So in this case I'd want an overall count of 3 as 'Bob' has Admin A and B roles as well as C & D, whereas Frank has just C & D.

Upvotes: 1

Views: 53

Answers (2)

sr28
sr28

Reputation: 5106

I finally came up with something that fitted my needs and realised I'd probably missed off some key info in the question, apologies. I used the formula below to create 2 columns that acted as flags. 

=IF($A3=$A2,"",IF(COUNTIFS($A:$A,$A3,$D:$D,$G$5)>0,IF(COUNTIFS($A:$A,$A3,$D:$D,$G$7)>0,"YES",""),""))

So in this scenario I would count the number of times a specified role e.g. 'Admin A' (cell G5) appeared for a given user. If this was greater than 0 then do another count for the second role that results in a duplicate e.g. 'Admin B' (cell G7). If this is greater than 0 then I can assume that the user has both roles.

I wrapped this all in a check to see if the user in the row above was the same as the current row, and if so don't do the calculation. This is to stop the flag appearing more than once for the same user. NOTE: this assumes the data is sorted on the user (column A in my formula).

I finally did a count of all the 'YES's in the columns that had my formula to work out how many would end up with duplicate roles, in the formula below this was column E:

=COUNTIF($E:$E, "YES")

Upvotes: 1

Ditto
Ditto

Reputation: 3344

I managed to get the result by breaking it down in smaller steps (mostly so you could see what was happening). At this point, not sure how "condense" the answer could get, but I'm sure you could condense some of it.

Using your original sample in columns A,B.

C2 : =CONCATENATE(A2,":",B2)

D2 : =RIGHT(B2,1)

E2 : =IF(D2="A","B",IF(D2="B","A",IF(D2="C","D",IF(D2="D","C","?"))))

F2 : =SUBSTITUTE(C2,CONCATENATE("Admin ",D2),CONCATENATE("Admin ",E2))

G2 : =MATCH(F2,C:C,0)

H2 : =IF(ISERROR(G2),0,1)

Copy C2:H2 down to C8:H8 ..

H1 : =SUM(H2:H8)/2

You could combine some or all of those into a single column .. however, that final SUM/2 will do the "magic" after you've basically "flagged" the rows that are "good to consider".

In summary: 1) id the mapping and relation of A<=>B OR C<=>D ... 2) then use MATCH to find another match and flag that row 3) finally count up the matches (and /2 .. since those are pairs)

Upvotes: 0

Related Questions