dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
607
share rss forum feed


aurgathor

join:2002-12-01
Lynnwood, WA
kudos:1
Reviews:
·Frontier Communi..

[Excel] finding duplicates

Let assume I have a column of numbers such as:
1591896
1550949
1626972
1627468
1627469
1622991
1625554
1627672
1624812
but a little more (~200) and I'd like to know if there are duplicates, and if yes, which ones.

TIA
--
Wacky Races 2012!



CylonRed
Premium,MVM
join:2000-07-06
Bloom County

Which version of Excel? 2007 and 2010 have Conditional Formatting (Home tab) and have it highlight cells with duplicate values. Then you can sort based on the highlight.
--
Brian

"It drops into your stomach like a Abrams's tank.... driven by Rosanne Barr..." A. Bourdain



aurgathor

join:2002-12-01
Lynnwood, WA
kudos:1

It's Excel 2000.


LLigetfa

join:2006-05-15
Fort Frances, ON
kudos:1

Can you import the data into MS Access? That's how I would do it.



CylonRed
Premium,MVM
join:2000-07-06
Bloom County

1 edit
reply to aurgathor

You can try this:
»uk.answers.yahoo.com/question/in···3AAb3x5X

quote:
Let's say you want to highlight the duplicates yellow.
1. Highlight the entire column yellow.
2. Data-Filter-Advanced Filter
- pick Filter the list, in place
- check Unique records only
(this will hide the duplicates)
3. Select the visible cells and turn off the highlighting
4. Data-Filter-Show All

All the records should now be displayed, with only the duplicates highlighted. I believe the first instance of a duplicate will not be highlighted, but the others will be.

--
Brian

"It drops into your stomach like a Abrams's tank.... driven by Rosanne Barr..." A. Bourdain

LLigetfa

join:2006-05-15
Fort Frances, ON
kudos:1

I don't have 2000 to test with but that works for me in Excel 2003.



brianmcd
Premium
join:2000-09-06
Norristown, PA
reply to aurgathor

Assuming that you have a Header on the 1st Row in A1

Sort the Numbers from Lowest to Highest
Add the following formula to B2:
=IF(A2=A1,"Dup","")
and then copy it to all the Rows with Numbers

If the Goal is to delete the Dups then copy column B and Paste Special-Values it back to Column B. Then sort by Column B and delete all of the Dups.



aurgathor

join:2002-12-01
Lynnwood, WA
kudos:1
Reviews:
·Frontier Communi..

The goal is not to delete anything -- I just want to know if there are dupes for some further study. I didn't specify in my OP, but the list cannot be sorted or changed in any way.
--
Wacky Races 2012!


LLigetfa

join:2006-05-15
Fort Frances, ON
kudos:1

said by aurgathor:

I just want to know if there are dupes for some further study.

Then what CyclonRed posted should fit the bill. You can undo the changes.
--
Strange as it seems, no amount of learning can cure stupidity, and formal education positively fortifies it. -- Stephen Vizinczey

avguser

join:2003-02-09
reply to aurgathor

Add a special formula in the column next to your data and use this formula in every row that has data. Assume data is in Column A

=countif(A:A,A2).

This formula will could the number of cells that have the same value. Any value greater than 1 indicates a duplicate



PhoenixDown
FIOS is Awesome
Premium
join:2003-06-08
Fresh Meadows, NY
kudos:1
Reviews:
·Verizon FiOS
reply to aurgathor

said by aurgathor:

The goal is not to delete anything -- I just want to know if there are dupes for some further study. I didn't specify in my OP, but the list cannot be sorted or changed in any way.

Add a column, enter =row()
copy/paste special -> values

This should let you sort the rows as suggested and when its time to put it back to normal, sort back to the way you found it using this new column.
--
1/22/2012 Delegate Count
Newt 25 | Romney 14 | Ron Paul 10 | Santorum 8


thephantom

join:2001-04-24
Alamo, CA
reply to aurgathor

use a pivot table. you will need a header row at the top of the column, then run a pivot table with the header in both the "row" and "data" area. Make sure the data area specifies "count of" and not "sum of" (the default), and you'll be able to see which are duplicates and how many times it is duplicated.



2kmaro
Think
Premium,ExMod 1 BC
join:2000-07-11
ColossalCave
kudos:1
reply to aurgathor

I would probably do it the way that avguser See Profile recommended - it's quick, non-data altering and you can just delete the column of formulas when you're done.

You can even filter the data based on the content of that column (greater than 1) and see just the duplicated entries.
--
...then THINK! again.



H2OuUp2
Happy to be here
Premium
join:2002-03-15
Oklahoma City, OK
reply to aurgathor

Use Conditional Formatting.

Enter this formula (assuming your data in in Column A)

=COUNTIF($A$2:$A$201,A2)>1

Be sure to highlight the entire section you are working on and use Formula Is as the condition and just pick a color.
--
He is no fool who gives up what he cannot keep, to gain what he cannot lose. - Jim Elliot