PDA

View Full Version : Solved: Match 3 cells?



blackie42
10-15-2008, 12:35 AM
Hi

I have a big list of names spread over 3 cells e.g.

Mr Jon Black

A lot of them are repeated and I'd like to remove the duplicates.

If it were one cell I'd maybe use =A1=A2 to get TRUE/FALSE but not sure
how to do for multi cells or if a macro would do it?

Any help appreciated

thanks
Jon

Bob Phillips
10-15-2008, 01:10 AM
In a helper column

=SUMPRODUCT(--($A$2:A2=A2),--($B$2:B2=B2),--($C$2:C2=C2))>1

and copy down.

This will show the second of any duplicates, then you can filter this helper column for TRUE and delete the visible rows.

slamet Harto
10-15-2008, 02:49 AM
Hi Bob,

Sorry to bothering you.
Can you advise, what is function for line -- and how we can use it?
As I can't find this function in excel help.
=SUMPRODUCT(--($A$2:A2=A2),--($B$2:B2=B2),--($C$2:C2=C2))>1

Thanks & Rgds,
Harto

georgiboy
10-15-2008, 03:06 AM
You could use the count function

=IF(COUNTIF(A:A,A1)>1,IF(COUNTIF(B:B,B1)>1,IF(COUNTIF(C:C,C1)>1,"Duplicate",""),""),"")

This will label duplicates with the word Duplicate.

hope this helps

Bob Phillips
10-15-2008, 03:14 AM
Hi Bob,

Sorry to bothering you.
Can you advise, what is function for line -- and how we can use it?
As I can't find this function in excel help.
=SUMPRODUCT(--($A$2:A2=A2),--($B$2:B2=B2),--($C$2:C2=C2))>1

Thanks & Rgds,
Harto

Harto,

it is all explained at this paper (http://www.xldynamic.com/source/xld.SUMPRODUCT.html)

blackie42
10-15-2008, 05:22 AM
Thanks Bob

As ever your solutions do the job - suspect you know excel better than the developers themselves!

regards

Bob Phillips
10-15-2008, 05:50 AM
Seriously, I have met some of the Excel developers, and they are not aware of the way that we in the real world use SUMPRODUCT and some of the other complex formulae combinations!