Finding duplicates in Multiple Columns

[JCS]

Good Morning All,

I have a spreasdheet with 3 columns. I would like to find the duplicate
entries in all 3 columns. I can find duplicate entries in 2 columns using
the Index and Match functions but don't know haw to find duplicates in 3
columns. Enclosed is a sample of what I would like to do:

Field 1 Field 2 Field 3 Duplicates
smith jones ellis ellis
ellis myers jacobs
daniles ellis gates

Any suggestions would be greatly appreciated. Thanks in advance!
John

 

[art]

Use conditional formatting to find duplicate values. You select conditional
values, and select highlight cell rules to find the duplicates.

 

[JCS]

Hi Art,

Thanks for the info. I know how to using conditional formatting but not for
highlighting duplicates. Can you give me a suggestion on how to do it?

Thanks,
John

 

[art]

Which version do you have? 2003 or 2007?

Hi Art,

Thanks for the info. I know how to using conditional formatting but not for
highlighting duplicates. Can you give me a suggestion on how to do it?

Thanks,
John

 

[art]

If you have 2007 then just select the button "conditional formatting" then
select highlight cell rules, then select duplicate values.

 

[JCS]

Art,

I am working with version 2003.

Thanks,
John

 

[art]

OK. Not a problem. Read this page, it explains step by step how to apply that.

http://office.microsoft.com/en-us/excel/HA011366161033.aspx

If you just want a quick fix, try the match function, where it would tell
you if there are any matches to the lookup value.

Let me know if this helps, by hitting this post was helpful, or not.

Good luck.

 

[JCS]

Art,

Yeah, I found this article and tried it before posting. It works great on 1
column but not on 3 unless I am missing something. At any rate, thanks for
the help.

John

 

[art]

Why don't you combine in a different sheet all three columns, and then check
for duplicates?

 

[Don]

try to copy the names in one colum and sort keeping the previous column name
=

A B
Field 1 Smith
Field 1 Ellis
Field 1 Danils
Field 2 Jones
Field 2 etc

Then when you sort by column B, you can put a formula in column C to check
if matched (maybe go as far as Data / Filter C)
=if(and(a1="Field 1",a2="Field 2",a3="Field3",A1=A2,A1=A3),"Match","no Match")

 

[Don]

sorry , make sure that in the if statement, you would check for the other two

 

[Teethless mama]

Try this:

=IF(ISERR(SMALL(IF((ISNUMBER(MATCH(Field_1,Field_2,0))*(ISNUMBER(MATCH(Field_1,Field_3,0)))),ROW(INDIRECT("1:"&ROWS(Field_1)))),ROWS($1:1))),"",INDEX(Field_1,SMALL(IF((ISNUMBER(MATCH(Field_1,Field_2,0))*(ISNUMBER(MATCH(Field_1,Field_3,0)))),ROW(INDIRECT("1:"&ROWS(Field_1)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

 

[JCS]

Hey Teethless mama,
Thanks for the help, but it didn't work. I appreciate the help!

John

 

[Teethless mama]

Worked on my machine

Hey Teethless mama,
Thanks for the help, but it didn't work. I appreciate the help!

John

 

[Max]

Assuming source data in A2:C4
Select A2:C4 (A2 active),
then apply CF using Formula is:
=COUNTIF($A$2:$C$4,A2)>1
Format to taste > Ok out

When I did the above over here,
the CF triggered "ellis" wherever it was

 

[JCS]

Max,

Thanks for the help. This is exactly what I'm looking for and it's easy!
John

 

[Max]

Welcome, John. Do spare a moment to press the "Yes" button below.