Just wondering how I can make Excel find all duplicate records in a column? For example, if 2079999-A occurs 3 times in the same column, I would like the 2nd and 3rd ones hilighted so I can erase them. At the end of the day I just want no duplicates.
I could manually do it but the records are in the thousands and I'm hoping there's an easier way?
Thanks!
if your values are in column A, B1
=countif($A1:A1,A1)>1
evalutes to TRUE for all but the first instance of each entry in the column, copy this formula down alongside your data, then filter it for 'true' then delete the records
An alternative approach is to use data -> advanced filter and select 'unique records only' - this only filters the data (doesn't delete)
hth
CC
If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.
Hi All,
I'm trying to make this work but with no luck:
B1
=countif($A1:A1,A1)>1
evalutes to TRUE for all but the first instance of each entry in the column, copy this formula down alongside your data, then filter it for 'true' then delete the records
Any idea what would prevent it from working? It is returning all values as FALSE though I know some should be TRUE.
then they are not exact matches find 2 you think are the same and test them eg =A2=A4
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hi
Maybe...
=COUNTIF(A:A;A1)>1
Copy down.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Does it matter that some of the numbers are 8 digits, some are 8 with a hyphen and another number etc.?
I tried changing it to a semicolon instead of comma but it didn't like that. Tried it again with a comma and it returned some "true" values this time but not the correct ones. Other thing I am wondering is if maybe some of the cells aren't formatted right? I notice a few of them have the "comment" icon and it says "this number is formatted as text or proceeded by an apostrophe". Is there an easy way to make sure those are all correct and eliminate that as a possible problem?
Hi
I think, it's time to upload a sample workbook!
R2_Sampled_Points_Feb_17_2012.xlsAttached is the spreadsheet. Basically I am trying to run the formula in column C on the data in column B. This info all came directly from GPS units, so I didn't do any formatting of cells or any such stuff.Originally Posted by Fotis1991
Not sure if relevant but I guess I should add it is off of Garmin GPS units, saved to ArcMap shapefiles (which creates a .dbf file, which can be saved as an Excel file).
Last edited by eyeball11; 02-22-2012 at 02:41 PM.
Hi
Something like this, is what are you looking for?
Hi,
I had a go with your attachements and found you had to remove the $ from the formular or else it did not allow for the first instance as being unique. Other than that it worked fine and solved an issue that bugged me for ages.
Thanks very much Fotis!
its still hard to tell perhaps something like this removes spaces and any thing with -XX
Hi, I work daily with this problem, the way I have resolved it is as follows,
Your Data in column A, insert new column at B, Sort A a-z acending. in B2 insert formula Exact B2 Exact as B1 copy down whole completed column, then filter "True" (All true's are duplicates, the first duplicate is ignored. These true's can be deleted. == No duplicates
When you've Done this a couple of times , takes seconds !!
are you sure? to correct fotis you need =IF(COUNTIF($B$2:B2,B2)>1,"Doumple","ok"),
why didn't you just use the remove duplicates option on the data tab?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks