Unusual filter request
 

In a list of numbers, I want to know which numbers appear more than once.
Any idea how to do this? I tried using DataFilterAdvanced Filter, and then
choosing unique records only, but that gives me a list of *all* the numbers,
while deleting the duplicate instances of those that appear twice (or more;
but I know they'd only appear twice). I need it the other way -- I need a
list of *only* the numbers that appear more than once. Any ideas or help you
can provide, I'd really appreciate it.

You can use an Advanced Filter to extract a list of duplicates.

In the criteria range, leave the heading cell blank
In the cell below, enter a formula that refers to the column you want to
check, e.g.: =COUNTIF(C:C,C2)1

Run the advanced filter, and check the Unique records only box



JVANSTEL wrote:
In a list of numbers, I want to know which numbers appear more than once.
Any idea how to do this? I tried using DataFilterAdvanced Filter, and then
choosing unique records only, but that gives me a list of *all* the numbers,
while deleting the duplicate instances of those that appear twice (or more;
but I know they'd only appear twice). I need it the other way -- I need a
list of *only* the numbers that appear more than once. Any ideas or help you
can provide, I'd really appreciate it.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi!

One way:

I assume your list of numbers is in Col A1:A1000 and col B is empty.

In B1 put =IF(COUNTIF($A$1:$A$1000,A1)=1,"",COUNTIF($A$1:$A$ 1000,A1))

Copy this down to B1000.

This will show alongside the multiple entries the number of times they
occur.

Alf