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

 

[zacemmel]

Chris,

You have mail.

 

[Chris Davison]

Zac,

your formula changes in cell B6

 

[zacemmel]

=IF(ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$13,$A$1:$A$13,0)>MATCH(B4,$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(B4,$A$1:$A$13,0),MATCH($A$1:$A$13,$A$1:$A$13,0),99999))))

This is the correct formula.
This message was edited by zacemmel on 2002-05-06 10:38

 

[Chris Davison]

On 2002-05-06 10:37, zacemmel wrote:
=IF(ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$13,$A$1:$A$13,0)>MATCH(B4,$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(B4,$A$1:$A$13,0),MATCH($A$1:$A$13,$A$1:$A$13,0),99999))))

This is the correct formula.
This message was edited by zacemmel on 2002-05-06 10:38

if I make cell A4 blank, I get nothing back except "mary" in B1

 

[Chris Davison]

also Zac, it might have been slightly useful to have told us that this is an array-entered formula, entered with CNTRL SHIFT ENTER, as oppose to a regular formula

:wink:

 

[zacemmel]

The data has to be contiguous and adjacent. I thought it was inferred that it was an array formula. I guess not

 

[Chris Davison]

okay, so *if* it's adjacent and contiguous and you don't mind editting the formula to fit your exact data range each time, remembering not to put it in the first row, I concur that it works.

Gotta admire your perserverance !