| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding & Replacing a list of Data in Multiple Columns | Excel Worksheet Functions | |||
| Finding duplicates based on 2 columns | Excel Worksheet Functions | |||
| Finding Duplicates | Excel Worksheet Functions | |||
| Finding Duplicates | Excel Worksheet Functions | |||
| Finding common data in multiple columns and rows in Excel | Excel Worksheet Functions | |||
Linear Mode
