1

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.

2
  • What about switching group and username columns? Mar 7, 2017 at 19:26
  • I would still have to look trough the whole table to find the duplicates.
    – ranbo
    Mar 7, 2017 at 19:52

1 Answer 1

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:

enter image description here

4
  • The thing is that one user can be a duplicate but within different sites or permission type. In order for that to work I would have to manually select the range of all groups and permissions type.
    – ranbo
    Mar 7, 2017 at 19:54
  • You have to repeat Sites, Groups, and permissions in that case and use countifs
    – yass
    Mar 7, 2017 at 20:23
  • you mean repeat item labels @yass?
    – ranbo
    Mar 7, 2017 at 20:25
  • In the same column repeat Site A Read and group1 for every username
    – yass
    Mar 7, 2017 at 20:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .