Finding duplicates in Multiple Columns
 

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

Finding duplicates in Multiple Columns
 

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

"JCS" wrote:

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

Finding duplicates in Multiple Columns
 

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" wrote:

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

"JCS" wrote:

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

Finding duplicates in Multiple Columns
 

Which version do you have? 2003 or 2007?

"JCS" wrote:

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" wrote:

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

"JCS" wrote:

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

Finding duplicates in Multiple Columns
 

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

"JCS" wrote:

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" wrote:

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

"JCS" wrote:

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

Finding duplicates in Multiple Columns
 

Art,

I am working with version 2003.

Thanks,
John

"JCS" wrote:

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" wrote:

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

"JCS" wrote:

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

Finding duplicates in Multiple Columns
 

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

http://office.microsoft.com/en-us/ex...366161033.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" wrote:

Art,

I am working with version 2003.

Thanks,
John

"JCS" wrote:

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" wrote:

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

"JCS" wrote:

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

Finding duplicates in Multiple Columns
 

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" wrote:

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

http://office.microsoft.com/en-us/ex...366161033.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" wrote:

Art,

I am working with version 2003.

Thanks,
John

"JCS" wrote:

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" wrote:

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

"JCS" wrote:

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

Finding duplicates in Multiple Columns
 

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

"JCS" wrote:

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" wrote:

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

http://office.microsoft.com/en-us/ex...366161033.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" wrote:

Art,

I am working with version 2003.

Thanks,
John

"JCS" wrote:

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" wrote:

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

"JCS" wrote:

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

Finding duplicates in Multiple Columns
 

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")

"JCS" wrote:

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

Finding duplicates in Multiple Columns
 

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

"Don" wrote:

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")

"JCS" wrote:

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

Finding duplicates in Multiple Columns
 

Try this:

=IF(ISERR(SMALL(IF((ISNUMBER(MATCH(Field_1,Field_2 ,0))*(ISNUMBER(MATCH(Field_1,Field_3,0)))),ROW(IND IRECT("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(INDIR ECT("1:"&ROWS(Field_1)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down


"JCS" wrote:

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

Finding duplicates in Multiple Columns
 

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

John

"Teethless mama" wrote:

Try this:

=IF(ISERR(SMALL(IF((ISNUMBER(MATCH(Field_1,Field_2 ,0))*(ISNUMBER(MATCH(Field_1,Field_3,0)))),ROW(IND IRECT("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(INDIR ECT("1:"&ROWS(Field_1)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down


"JCS" wrote:

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

Finding duplicates in Multiple Columns
 

Worked on my machine


"JCS" wrote:

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

John

"Teethless mama" wrote:

Try this:

=IF(ISERR(SMALL(IF((ISNUMBER(MATCH(Field_1,Field_2 ,0))*(ISNUMBER(MATCH(Field_1,Field_3,0)))),ROW(IND IRECT("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(INDIR ECT("1:"&ROWS(Field_1)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down


"JCS" wrote:

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

Finding duplicates in Multiple Columns
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JCS" wrote:
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

Finding duplicates in Multiple Columns
 

Max,

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

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JCS" wrote:
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

Finding duplicates in Multiple Columns
 

Welcome, John. Do spare a moment to press the "Yes" button below.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JCS" wrote:
Max,
Thanks for the help. This is exactly what I'm looking for and it's easy!
John