Tags: documents, duplicate, entries, excel, exclude, formula, individually, microsoft, msdn, multiple, numbered, software, worksheet

Excel formula to exclude all data except multiple entries?

On Microsoft » Microsoft Excel

1,702 words with 3 Comments; publish: Thu, 22 May 2008 06:24:00 GMT; (30662.50, « »)

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.

All Comments

Leave a comment...

  • 3 Comments
    • 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

      "cd_gilbert" wrote:

      > 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.

      #1; Thu, 22 May 2008 06:25:00 GMT
    • 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

      On Dec 13, 1:37 pm, cd_gilbert <cd_gilb....excel.todaysummary.com.discussions.microsoft.com>

      wrote:

      > 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.

      #2; Thu, 22 May 2008 06:26:00 GMT
    • Sorry that should read "Place the formula "=COUNTIF($A2:$A

      $11000,A2)=1" in cell B2 and fill down"

      Thx

      #3; Thu, 22 May 2008 06:27:00 GMT