1

I have the following data to start with:

Sr. No  Conf. By    Prod
1           A         X
2           B         Y
3           A         Z
4           B         X
5           A         X
6           B         Y
7           A         Y
8           B         M
9           A         N
10          B         O

I am interested in column Prod. I want only those values which have been repeated more than once in column Prod.

This is the output I expect after the data has been filtered:

 Sr. No Conf. By    Prod
   1        A        X
   2        B        Y
   4        B        X
   5        A        X
   6        B        Y
   7        A        Y

Here is what I have done for now. Simply highlighting the duplicate values isn't of much use to me. So I created a pivot table as follows:

Pivot table with "Prod" as row labels & a count of "Prod"

This gives me a count of how many times each element in Prod has been repeated. If I double click on each of the numbers in the Count of Prod column it gives me the details of those entries from the original table.

However, I want this for all the items where the count is more than 1.

Any other ideas on how I can get the desired output?

1

In D2 enter:

=COUNTIF(C:C,C2)

and copy down. Then apply a filter on column D to hide the 1's:

enter image description here

If you want to hide items appearing twice, hide any 2's as well.

| improve this answer | |
  • This is almost exactly the answer I was looking for. However, if I have a really long column, it is hard to visualize the repeated items in Prod together. So I just sorted the Prod column alphabetically and that worked! – Achyutha Mohan Oct 12 '15 at 16:32
0

You're very nearly there.

Right-click one of the Prod labels in your PivotTable (eg cell A5 in your example) and select Filter, then Value Filters. Set Count of Prod to be greater than 1.

That's it!

| improve this answer | |
  • I'd done that. However, I cannot see the other details of the filtered set unless I double click on the count numbers. And I have to do this for every case where the count is greater than 1 to see the details. Is there a way I can get all the details of my original data after I apply the filters? – Achyutha Mohan Oct 12 '15 at 16:20
  • Ah I see. I thought you wanted to do this in the PivotTable. In that case Gary's Student's answer is the one you want! – Andi Mohr Oct 12 '15 at 16:25
  • Yes, that's really close. However, if my column length is really long it gets difficult to see the repeated items grouped together. – Achyutha Mohan Oct 12 '15 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.