• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Remove duplicates (case sensitive)

R

Raesu

Member
Hello,


Surprisingly I couldn't find an article or forum posts on this topic. I got some clues from other sites but can someone validate this to make sure it makes sense?


I'm using SalesForce IDs (different IDs can vary by the case of a letter) so I'd like to remove any lines with duplicate IDs. This is what I am using:

[pre]
Code: 
=SUMPRODUCT(--(EXACT(F3:$F$46856,F2)))
[/pre]

F2 contains the ID, and I hope that any line that has a value over 0 I can delete, while still keeping one copy of it. (the last occurrence should be 0). Any tips here? I have done some spot checking and it seems to work.


Raesu
 
http://office.microsoft.com/en-us/excel-help/filter-for-unique-values-or-remove-duplicate-values-HP010073943.aspx
 
=REPT(A2;SUMPRODUCT(--EXACT($A2;$A$1:$A1))=0)


http://www.excelforum.com/excel-general/742505-removing-duplicates-in-a-case-sensitive-manner.html
 
Good day Montrey

I have visited the site you linked to and read and reread the formula you posted in you last reply...but still no wiser, could I impose on you time to break the formula down and explain what is happening. Thank you
 
This does the same thing if Rept is too confusing.

=IF(SUMPRODUCT(--EXACT(A2,$B$1:B1)),"",A2)


The formula cycles through the range to see if there is an EXACT match. If there is, it will put a blank in the cell, if there is not a duplicate it will put the cell value we are looking up.


It is kind of like an array. The double (--) makes the formula cycle through the range.
 
Montrey I think I have an understanding, I will make a practice sheet with false data and see what happens when I use the formula, I find it easier to understand it when I use practice sheets than try to imagine how it would work
 
So does my formula not work? Seem to be pretty similar.


edit: I see what the formula is doing, but I'm working with records here so I don't just want a list of unique values. I'm looking for something I can filter on, and delete all those duplicate records. Any tips with that?
 
Hey Raesu try this.


Use conditional formatting with this formula.

=IF(SUMPRODUCT(--EXACT(A2,$B$1:B1)),true,false)


Then select all the formatted records it finds and delete them.
 
You must log in or register to reply here.
Back
Top