Finding duplicates in Multiple Columns

  • Thread starter Thread starter JCS
  • Start date Start date
J

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
 
Use conditional formatting to find duplicate values. You select conditional
values, and select highlight cell rules to find the duplicates.
 
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
 
Which version do you have? 2003 or 2007?

JCS said:
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
Click to expand...
 
If you have 2007 then just select the button "conditional formatting" then
select highlight cell rules, then select duplicate values.
 
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
 
Why don't you combine in a different sheet all three columns, and then check
for duplicates?
 
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")
 
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
 
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
 
Back
Top