Duplicates

zacemmel · May 2nd, 2002, 12:02 PM

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 ]

waderw · May 2nd, 2002, 12:45 PM

What a cluster! How in the world did you come up with that one?

Good job if it works.

sen_edp · May 2nd, 2002, 02:42 PM

HELLO THERE ZACEMMEL

PLEASE EXPLAIN TO ME HOW ARE THE DUPLICATES
REMOVED WITH THIS FORMULA?

REGARDS

ANDREAS

GeorgeB · May 2nd, 2002, 02:43 PM

OK I'll bite. Where do you put this jewel??

Ricky Morris · May 2nd, 2002, 03:23 PM

I'm stumped at why you think this formula (or any formula) would remove duplicates since formula do not modify the contents of the spreadsheet they only return a result.

sen_edp · May 2nd, 2002, 03:25 PM

Hello there

I Agree with Ricky

Formulas don't remove duplicates !!!

Regards

Andreas

zacemmel · May 2nd, 2002, 03:39 PM

It doesn't remove duplicates from the existing list. It removes duplicates from the new list that it creates. It works, trust me.

RET79 · May 2nd, 2002, 04:08 PM

It does work and is amazing stuff I think.

RET79

Chris Davison · May 2nd, 2002, 04:25 PM

Zacemmel,

which cell would this start in ?

and which range would have the potential list of duplicates ?

I was just trying to test this impressive formula but couldn't quite get the right ranges

thanks
Chris

sen_edp · May 2nd, 2002, 04:29 PM

Hello Zacemmel

I would like more details on this formula please.

Andreas

May 2nd, 2002, 02:43 PM	#4
GeorgeB Board Regular Join Date: Feb 2002 Location: Las Vegas Nevada USA Posts: 240	OK I'll bite. Where do you put this jewel?? __________________ George Learn to listen. Opportunity sometimes knocks very softly.

May 2nd, 2002, 03:23 PM	#5
Ricky Morris Board Regular Join Date: Mar 2002 Posts: 363	I'm stumped at why you think this formula (or any formula) would remove duplicates since formula do not modify the contents of the spreadsheet they only return a result. __________________ It's never too late to learn something new. Ricky

May 2nd, 2002, 04:29 PM	#10
sen_edp Board Regular Join Date: Mar 2002 Location: Hellas Posts: 553	Hello Zacemmel I would like more details on this formula please. Andreas __________________ Best Regards Andreas

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 2nd, 2002, 12:02 PM	#1
zacemmel Board Regular Join Date: Apr 2002 Location: Redmond, WA Posts: 636	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 ]

May 2nd, 2002, 12:45 PM	#2
waderw Board Regular Join Date: Apr 2002 Posts: 85	What a cluster! How in the world did you come up with that one? Good job if it works.

May 2nd, 2002, 02:42 PM	#3
sen_edp Board Regular Join Date: Mar 2002 Location: Hellas Posts: 553	HELLO THERE ZACEMMEL PLEASE EXPLAIN TO ME HOW ARE THE DUPLICATES REMOVED WITH THIS FORMULA? REGARDS ANDREAS

May 2nd, 2002, 03:25 PM	#6
sen_edp Board Regular Join Date: Mar 2002 Location: Hellas Posts: 553	Hello there I Agree with Ricky Formulas don't remove duplicates !!! Regards Andreas

May 2nd, 2002, 03:39 PM	#7
zacemmel Board Regular Join Date: Apr 2002 Location: Redmond, WA Posts: 636	It doesn't remove duplicates from the existing list. It removes duplicates from the new list that it creates. It works, trust me.

May 2nd, 2002, 04:08 PM	#8
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	It does work and is amazing stuff I think. RET79

May 2nd, 2002, 04:25 PM	#9
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Zacemmel, which cell would this start in ? and which range would have the potential list of duplicates ? I was just trying to test this impressive formula but couldn't quite get the right ranges thanks Chris