Hello everybody
I have values in Range("G4:G79") There are duplicates for every value ..
I want to get rid of duplicates except for the first value
for example : In range("G4:G22") there is the value 15 I want to clear all the values of 15 except the first which is in G4 and so on for the rest of values
And if the value is unique remains as it ..
The results should be in its place not to collect all uniques ...
G4 = 15 and blank cells till G23 =20
Here's an attachment
Hi there,
Excel 2007 introduced a Remove Duplicates option in the Data Tools tab of the Data ribbon.
See my post here for more info about this and some other options as well.
Regards,
Robert
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
Or you could put this in a cell where you wantthe answers to be, and copy down...
=IFERROR(INDEX($G$4:$G$79,MATCH(0,INDEX(COUNTIF($H3:H$3,$G$4:$G$79),0,0),0)),"")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Yes, your formula was one of the nifty options I was referring to in my reply aboveOr you could put this in a cell where you wantthe answers to be, and copy down...
=IFERROR(INDEX($G$4:$G$79,MATCH(0,INDEX(COUNTIF($H3:H$3,$G$4:$G$79),0,0),0)),"")
Thank you for offering help
I told that (The results should be in its place not to collect all uniques )
Every value in its place !!!
OK, I looked at your post there and did not see that 1, and looked again now, and saw I posted pretty much the same thing there tooOriginally Posted by Trebor76
Yes, your formula was one of the nifty options I was referring to in my reply above
Presumably, you will have other data associated with those values, what would you want done with that?
Or, if this is just a straight-forward list of values, you could use that formula, copied down, then copy/paste values over the original data, delete what remains and delete the formulas
Mr. FDibbins I just want to clear duplicates without removing cells between distict values.. !! As there are associated data to each value and I want my data to be obvious .. and by clearing the duplicates I'll get my target
OK another approach...
Put this in a helper anc copy down...
=IF(COUNTIF($G$4:G4,G4)>1,1,"")
then apply filters, uncheck everything but 1(in other words, uncheck Blank) and then delete all teh rows that remain - then remove filters and delete the helper
Or with some small changes to my macro response to your thread Extract Unique values in range try this:
Robert
Please Login or Register to view this content.
Remove Duplicates (from Excel 2007) has a bug and should only be used if one feels lucky about one's dataset or if reliability of results is unimportant.
Given there's plenty of methods available in Excel for removing duplicates maybe better to avoid that approach entirely.
To illustrate, type the following values in a column and see how many duplicates you can remove:
9
10
10a
9
10
For the OP's problem here, a simple boolean would suffice.If text exists in the data and it doesn't matter about duplicate text then can include On Error Resume Next, or If IsNumeric(c) Then ...Please Login or Register to view this content.
If are interested in also removing any duplicate text then can use something almost equally simple, such asAlso, as noted, variety of other methods, collection, dictionary, direct comparison, etc.Please Login or Register to view this content.
Yes, it seems the problem is when there's both numeric and alphanumeric items in the range as if you put a single quote in front of the 9's and 10's in your dataset the functionality works as expected. I think that as the key in my New Collection method is always a string it should be OK - I'll test it later to confirm.Remove Duplicates (from Excel 2007) has a bug and should only be used if one feels lucky about one's dataset or if reliability of results is unimportant.
Nice tip kalak
Robert
I have not experienced any problems with Remove Dupicates, and I have used it plenty.
In your sample, RD produced this...
9 9 10 10 10a 10a 9 10
Looks like it worked just fine?
Mr. Trebor76 and Mr. kalak
your solutions are wonderful thank you very for your great help
You're welcome and thanks for marking the thread as solved (and for adding to my reputationMr. Trebor76 and Mr. kalak
your solutions are wonderful thank you very for your great help
@Ford
Then you're in a fortunate position. Trebor's feedback indicated that he seemed rather less confident than do you.
Using Remove Duplicates on that dataset for me, with 2 different computers (32 bit and 64 bit, both with Win7 and Excel 2007) both VBA and from the ribbon claimed no duplicates were found. I've heard the problem wasn't fixed in Excel 2010, but have no info about 2013. Do you have 2013? Seems not, from your avatar. Win8 or other OS perhaps?
That problem arises most obviously with numeric and numeric-alpha combinations which are not uncommon in some projects.
To get examples of non-working combinations I used the testdata code as below. On my computers, all duplicates were removed about 50% of the time. So if you'd like to run that randomizing testdata code say 10 times and have 100% success then I guess you'd be happy to advise other users that the method seems generally OK. From my experience, I'm unable to do that.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks