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]

On 2002-05-02 17:25, Ricky Morris wrote:
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.

Hi Ricky,

With Zacemmel's formula, would type =A1 in cell B1 and his formula in B2 and copy down the list. For the one I posted, type it in B1 and copy down the list.

Bye,
Jay

 

[Ricky Morris]

Zacemmel, this is pretty good. I'm impressed. However, I used the following test data and the formula yeilded incorrect result.

A1 jan
A2 feb
A3 mar
A4 apr
A5 may
A6 jun
A7 jul
A8 aug
A9 sep
A10 oct
A11 nov
A12 dec
A13 jan
A14 ricky

The last unique cell, A14, was not displayed by the formula.

 

[zacemmel]

You have to extend the range

 

[Chris Davison]

Zac,

extending the range as per your suggestion to the flaw posted by Ricky :

=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)))))

does not work

"Ricky" does not show up as a unique entry.
Neither do any items in the list that proceed a numeric duplication, as I pointed out earlier.....

try replacing "feb" with "111"

then replace "jul" with "111"

everything below *will* disappear.

I hope this filters through... you don't seem to want to understand !!

good luck

 

[zacemmel]

I understand my own formula. The range HAS to be full. If the range specified in the formula isn't full, it will not work. If the range is full, my formula works. You people just aren't using it correctly.

 

[Mark W.]

Here's another approach...

See my posting at http://www.mrexcel.com/board/viewtopic.php?topic=7253&forum=2

 

[zacemmel]

The purpose of my post is to get rid of duplicates without using VBA, sorting, or filtering without getting spaces between records.

 

[Chris Davison]

Zac,

it's a wonderful formula, I like it.

Alas, it doesn't quite work.

I just filled my range per my example above and it fails.

Would you like to post an example range that does work so that *we* can see where *us people* are going wrong ?

Full marks for the time and trouble you've invested.... let's finish this off for posterity.

Chris

 

[Mark W.]

On 2002-05-03 14:54, zacemmel wrote:
The purpose of my post is to get rid of duplicates without using VBA, sorting, or filtering without getting spaces between records.

Right... I didn't use VBA, AutoFilter, or the Data | Sort menu command. It works effortlessly... even on 10,000 rows.

 

[Ricky Morris]

Here's a solution taken from John Walkenbach's Excel 2000 Formulas book.

The source list containing the duplicates is named data. This is a multi-cell array. It should be entered at once in a range with the same number of rows as "data". Like all arrays it should be entered by pressing Ctl-Shift-Enter together.

=INDEX(data,SMALL(IF(MATCH(data,data,0)=ROW(INDIRECT("1:"&ROWS(data))),MATCH(data,data,0),""),ROW(INDIRECT("1:"&ROWS(data)))))