Excel formula to exclude all data except multiple entries?

[cd_gilbert]

I have about 11,000 documents listed in a worksheet that are individually
numbered, and want find duplicate entries in the worksheet. I need to create
a formula that will allow me to see ONLY the duplicates.

 

[CLR]

If this is something you have to do regularly, I would recommend Jim Cone's
fine commercial Add-in called XLCompanion. It's available at

http://www.realezsites.com

Vaya con Dios,
Chuck, CABGx3

 

[JP]

Here's one way.

Let's say your data is in A1:A11000, with headers in row 1

1. Sort your data by column A (ascending)
2. Place the formula "=COUNTIF($A2:$A$11000,A2)=1" in cell B1 and fill
down
3. Sort your data by column B
4. You can delete all the rows that say "FALSE" in column B, those are
the dupes

I tested this on a small sample and it worked well.

Remember you must sort ascending first otherwise you can miss out on
some dupes.

HTH,
JP

 

[JP]

Sorry that should read "Place the formula "=COUNTIF($A2:$A
$11000,A2)=1" in cell B2 and fill down"


Thx