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

 

[Jay Petrulis]

Hi,

Enter the following into cells a1:a5
{1,2,3,1,5}

The formula will not return the 5.

Another way...

Data in column A
Enter the following in B1 and copy down.
=IF(COUNTIF(A:A,A1)=1,A1,IF(COUNTIF(A$1:A1,A1)=1,A1,""))

 

[sen_edp]

Hello zacemmel

very nice formula
Nice Work Man !!

 

[zacemmel]

Jay,

Your method doesn't put the data in adjacent cells. If there are duplicates, it leaves blank cells in their places :/

 

[Chris Davison]

=IF(ROW()=ROW($A$1),$A$1,IF(ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$50,$A$1:$A$50,0)>MATCH(B1,$A$1:$A$50,0),MATCH($A$1:$A$50,$A$1:$A$50,0),99999)))),"",INDIRECT("a"&MIN(IF(MATCH($A$1:$A$50,$A$1:$A$50,0)>MATCH(B1,$A$1:$A$50,0),MATCH($A$1:$A$50,$A$1:$A$50,0),99999)))))

in B2 and copied down



are you sure about the "MATCH" using a false arguement ? I'm getting weird result with just text now

 

[davers5]

The reason some of us would "resort" to writing our own function is that this formula is a mess and most people on the board can't understand it at all, and these are advanced excellers.

In many cases it is cleaner and easier to write a function or procedure.

Dave

 

[zacemmel]

By the way, the range must be complete, or my formula will not work. All cells in the range A1:A13 must be filled in.

 

[zacemmel]

Yah, ok Davers.

 

[Jay Petrulis]

On 2002-05-02 16:14, zacemmel wrote:
Jay,

Your method doesn't put the data in adjacent cells. If there are duplicates, it leaves blank cells in their places :/

Agreed.

Yet, it doesn't have a problem with the dataset I posted. Once a duplicate value is found, any subsequent unique values are not returned with your formula.

How about Advanced Filter? No VBA needed, and though you may get a duplicate "category" header, it works fine.
This message was edited by Jay Petrulis on 2002-05-02 16:23

 

[zacemmel]

Jay,

The advanced filter isn't full-proof. I have seen that POS break lots of times.

 

[Ricky Morris]

OK I'm lost. I have Jan, Feb... etc in cells A1 thru A13. Which cell(s) should I put this formula to display the unique values.