Find and display only duplicates

[Sarah_Lund]

I need help please! I have a worksheet with multiple rows/columns of data. I
need to look at the text in each cell in column B (which is already sorted
alphabetically) and if a cell is repeated, to display only the rows in which
the cells in a column are duplicates.

For example below I would only want to to display the two rows that the text
Cory is repeated.
Col A Col B
* Aaron
* Ben
* Cory
* Cory
* David
* Evan

I have not been able to figure this out on my own. Any help is greatly
appreciated.
Thank you,
Sarah

 

[Joel]

Usually people do this will formulas on the worksheet and use Autofilter.

1) in column c put Row 2 and copy down the column

=if(countif($B:$B,B2) > 1,true,false)

2) Select column C. On menu Data - Filters - AutoFilter

3) Now select True on AutoFilter to get duplicates

 

[Norman Jones]

Hi Sarah,

In C2, enter the following formula:

=COUNTIF($B$2:$B$11,B2)>1

and drag the formula down as far as needed.

Now autofilter columns A:C of your data,
using a criterion of true for the third
column of the autofilter.

Now only data rows will be visible for
which there is a duplication of column C
data

 

[Ron Coderre]

Here's one approach...

With the Col_B sorted list beginning in B10
and B10 as the column heading (eg name)

A1: DupeTest
A2: =OR(B11=B10,B11=B12)

Select from B10 down through the last data cell of Col_B

From the Excel Main Menu:
<data><filter><advanced filter>
List Range: (already selected)
Criteria Range: $A$1:$A$2
Click [OK]

Only the contiguous duplicate values will be displayed.

Is that something you can work with?
Post back if you have more questions.
-------------------

Regards,

Ron
Microsoft MVP - Excel

 

[Sarah_Lund]

This works great. Is there any way to do this with a macro so the enduser
doesn't have to do these steps each time they get an updated excel file?

Thank you,
Sarah

 

[Norman Jones]

Hi Sarah,

If you need to automate this, turn on the
macro recorder and perform the necessary
operations manually. This will provide you
with code which may be edited to afford
more generic application.

If you experience problems in editing the
recorder code, post back with the
problematic code.

 

[Sarah_Lund]

This worked exactly how I wanted.
Thank you!
Sarah