Hi,
I am adding entries to an existing file and need to remove any duplicates based on an index number which is unique for each item.
I know there's a function to do this in the new version of Excel but is there a way to do it in Excel 2000?
Hi,
I am adding entries to an existing file and need to remove any duplicates based on an index number which is unique for each item.
I know there's a function to do this in the new version of Excel but is there a way to do it in Excel 2000?
Hello IM22,
U can use contif function to count how many times the value is in list, so using a fill u can exclude the rows with 2 or more.
Cya,
Thanks for the reply, can you expand on it at all? I can't see how it would work, I tried it but the criteria seems to require that you state the value required which would mean I could only check for one number.
If your values are in A2:A100, in B2
=COUNTIF($A$2:A2,A2)>1 dragged down.
(replace comma with semicolon depending on regional settings)
This will give True or False. Then filter on True and delete those rows.
Does that help?
ChemistB
My 2¢
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Hi
Another option, without filtering, is this(In B2 and copy down).
Hope, that helps you.PHP Code:
=IF(MAX($B$1:$B1)=MAX($A:$A);"";SMALL($A:$A;1+COUNTIF($A:$A;"<="&N($B1))))
Edit: Change semi-colons, to comma, if you have to do it.
Last edited by Fotis1991; 04-09-2012 at 10:23 AM. Reason: Edit.
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.
Thanks also to Fotis1991, I tried your solution but I just got a column of zeros but it could have been something I did wrong.
Take a look to the example, pls.
See if this, is Ok, for you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks