Duplicates

[zacemmel]

I have seen several posts on the forum regarding duplicates. Most peope resort to UDFs and VBA to remove duplicates. That isn't neccesary. You can remove duplicates with the functions that are built-in to Excel.

Check out the formula I created below:

=IF(ROW()=ROW($A$1),$A$1,IF(ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$13,$A$1:$A$13,0)>MATCH(B1,$A$1:$A$13,0),MATCH($A$1:$A$13,$A$1:$A$13,0),99999)))),"",INDIRECT("a"&MIN(IF(MATCH($A$1:$A$13,$A$1:$A$13,0)>MATCH(B1,$A$1:$A$13,0),MATCH($A$1:$A$13,$A$1:$A$13,0),99999)))))
This message was edited by zacemmel on 2002-05-02 11:02

 

[Peter100]

Same question as George,
Where do you put this wonderful formula

 

[zacemmel]

The range needs to be a1:a13, b1=a1 and then paste that formula into b2. That should work.

 

[Chris Davison]

nice work !

are you looking to extend it to numeric values also ?

 

[zacemmel]

It already works for numeric values, doesn't it?

 

[Chris Davison]

sort of....

it puts the numeric value as a unique value but then blanks everything underneath

very close....

 

[zacemmel]

The point of the formula is to mirror a range, but remove duplicates. The formula is blank below because it doesn't find anymore data that is "unique."

 

[Chris Davison]

I wouldn't panic, it looks like you've broken the back of the logic with that formula...... good stuff

will you repost when perfected ?

I'd love to see it

 

[zacemmel]

If I don't know what's wrong, how can I fix it?

 

[Chris Davison]

yep, I agree

if I try this though:

Mary
Had
222
a
little
lamb
its
777
222
fleece
was
white
as
snow

I get this in column B :

Mary
Had
222
a
little
lamb
its
777

only


(I have to sign off and go to bed now, but I should say this is a very impressive formula...... nice work !)

 

[zacemmel]

Oh, you just have to change the range in the formula to check for more duplicates. It's currently only going from A1:A13.