removing duplicates in a case-sensitive manner

**SIFT-MS88** · 08-20-2010, 12:53 PM

Hi everyone,

I don't imagine my problem is too difficult to solve, however, I can't find anything about it on google. Basically, I have a column where the entries are strings of text. I want to remove all duplicates in the column in a case-sensitive manner. Unfortunately, the remove duplicates function in excel treats, for example, "LRV" and "LRv" as duplicates when in fact these should be considered unique. Any suggestions would be very much appreciated.

**DonkeyOte** · 08-20-2010, 01:09 PM

Are you looking to do this with VBA or with formula / menu functionality ?

If your strings were in say A1:A10 then

B1: =$A1
B2: =REPT($A2,SUMPRODUCT(--EXACT($A2,$A$1:$A1))=0)
copied down

The values "left behind" in Col B are the unique values

edit: B2 formula modified per posts 3 & 4

**darkyam** · 08-20-2010, 01:40 PM

DO, I tried posting your formula into a workbook because I was interested to see how it worked. When I did, I got some duplicates mixed in and a couple unique values left out. The most likely explanation is that I did something wrong, but I can't figure out what. Could you please see the attached? Thanks.

Also, I played around with it to find an alternative just so I'd have one for the future and this seemed to work in B2: =IF(SUMPRODUCT(--EXACT(A2,$B$1:B1)),"",A2)

**DonkeyOte** · 08-20-2010, 01:43 PM

Originally Posted by darkyam

I got some duplicates mixed in and a couple unique values left out. The most likely explanation is that I did something wrong, but I can't figure out what.

No - error all mine - the reference to A1 in the REPT should of course be A2 - I will modify
(should test first eh?)

**zbor** · 08-20-2010, 01:45 PM

change formula into:

=REPT(A2;SUMPRODUCT(--EXACT($A2;$A$1:$A1))=0)

**darkyam** · 08-20-2010, 01:46 PM

I feel like a dunce now. It makes so much sense after seeing it with the minor correction. It's what I get for doing Excel on 3 hours of sleep. Thanks for the explanation.

**SIFT-MS88** · 08-20-2010, 03:36 PM

Darkyam, could you provide me with a brief explanation of what's going on in this formula (the one that's B2: =IF(SUMPRODUCT(--EXACT(A2,$B$1:B1)),"",A2)? I drew up an example and used it, and it seems to work, but I need to understand it a little better seeing as I'm using this for research and I can't make any mistakes! Thanks.

**darkyam** · 08-20-2010, 03:59 PM

We'll start from inside out. Exact compares two cells and sees if they are precisely the same, including upper and lower case. Because it is only supposed to be a 1:1 comparison, DO cleverly got around that by putting it in a Sumproduct formula, which, with the double unary operators (--), forces Excel to compare A2 to all of the different values from B1 through whatever cell is just above the one the formula is in, return 1 for each match and 0 for each non-match, and add them up. The Sumproduct formula itself returns the total number of matches.
In the test of an IF statement, 0 is false and all other values are true, so any number of matches returns true. The IF statement simply returns the value in A if false and a blank if true. DO's formula is the same principle; he just used Rept instead of an IF function. Does this make sense?