Solved: Match 3 cells?

[blackie42]

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]

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]

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]

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]

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

[blackie42]

Thanks Bob

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

regards

[Bob Phillips]

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!