| [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! |
|
 CylonRedPremium,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 |
|
|
|
| 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. |
|
CylonRedPremium,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. |
|
 brianmcdPremium
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. |
|
| 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 |
|
| 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-- Wants FIOSPremium
join:2003-06-08
Fresh Meadows, NY
kudos:1 | 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
|
|
| 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. |
|
 2kmaroThinkPremium,ExMod 1 BC
join:2000-07-11
ColossalCave | reply to aurgathor
I would probably do it the way that avguser  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. |
|
 H2OuUp2Happy to be herePremium
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 |
|
