Find and display only duplicates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sarah_Lund - 18 May 2008 03:01 GMT

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

Reply to this Message

Joel - 18 May 2008 03:13 GMT

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

> 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
[quoted text clipped - 15 lines]
> Thank you,
> Sarah

Reply to this Message

Norman Jones - 18 May 2008 03:16 GMT

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

---
Regards.
Norman

>I need help please! I have a worksheet with multiple rows/columns of data.
>I
[quoted text clipped - 18 lines]
> Thank you,
> Sarah

Reply to this Message

Sarah_Lund - 18 May 2008 13:34 GMT

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

> Hi Sarah,
>
[quoted text clipped - 38 lines]
> > Thank you,
> > Sarah

Reply to this Message

Norman Jones - 18 May 2008 14:25 GMT

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.

---
Regards.
Norman

> 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?
[quoted text clipped - 46 lines]
>> > Thank you,
>> > Sarah

Reply to this Message

Sarah_Lund - 19 May 2008 18:37 GMT

This worked exactly how I wanted.
Thank you!
Sarah

> Hi Sarah,
>
[quoted text clipped - 62 lines]
> >> > Thank you,
> >> > Sarah

Reply to this Message

Ron Coderre - 18 May 2008 03:24 GMT

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

> I need help please! I have a worksheet with multiple rows/columns of data.
> I
[quoted text clipped - 18 lines]
> Thank you,
> Sarah

Reply to this Message