Which excel formula to use?

Question

I created a pivot table based on some information and it is looking like this:

+------------+---------+---------+---------+
|    Site    |  UserP  |  Group  | Username
+------------+---------+---------+---------+
| Site A     |  Read   | Group1  | Michael |
|            |         |         |         |
+------------+---------+---------+---------+
|            |         |         | John    |
|            |         |         |         |
+------------+---------+---------+---------+
|            |         |         | Philip  |
|            |         |         |         |
+------------+---------+---------+---------+
|            |  Read   | Group2  | Philip  |
|            |         |         |         |
+------------+---------+---------+---------+

It basically shows everybody that has permission for site A, its type and where its coming from.

The point is to find duplicates, like "Philip" in the example above. He has the same type of permission from two groups, so we need to leave him only to one group.

How can I filter all duplicates? That is duplicates in the "Username" column within the same Site and type of permission?

At first I thought this was easy, as I could just filter per Site. But then I would have to go one by one filtering and looking for duplicates(I think, correct me if I am wrong and theres a easier way)

I am only starting with pivot tables so sorry if this is a dumb question.

Thanks in advance.

Answer 1

Use the initial Data and group it like the picture below You have to write:
Site A for each username
Read for each username
Group1 for each username
John and Philip like Michael in your example
=COUNTIFS($D$2:$D$11,D2,$B$2:$B$11,B2,$A$2:$A$11,A2)

Like the following: