| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Marking duplicate entries based on multiple columns
Hi all,
I have a list of files that I'd like to mark the duplicates. I can't use just the file name because some files have the same name but aren't the same file. As I understand it COUNTIF can't be used with multiple comparisons. I've tried CONCATENATE several of the columns and then using COUNTIF but it doesn't seem to work. Example of what I'm looking for.... File A, 400k, 9/23/09, Duplicate File A, 450k, 9/23/09 File A, 400k, 9/23/09, Duplicate Thanks in advance Toney |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Marking duplicate entries based on multiple columns
Try the below(all in one line)
=IF(SUMPRODUCT(($A$1:$A$100=A1)*($B$1:$B$100=B1)*( $C$1:$C$100=C1))1,"Duplicate","") If this post helps click Yes --------------- Jacob Skaria "Toney" wrote: Hi all, I have a list of files that I'd like to mark the duplicates. I can't use just the file name because some files have the same name but aren't the same file. As I understand it COUNTIF can't be used with multiple comparisons. I've tried CONCATENATE several of the columns and then using COUNTIF but it doesn't seem to work. Example of what I'm looking for.... File A, 400k, 9/23/09, Duplicate File A, 450k, 9/23/09 File A, 400k, 9/23/09, Duplicate Thanks in advance Toney |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Marking duplicate entries based on multiple columns
DOH!
I didn't carry the function down the entire column. Once I did, using CONCATENATE and COUNTIF worked. Well if anyone else needs this info what I did was.... In D1 =CONCATENATE(A1,B1,C1) where A1 is name, B1 is size, C1 is date. In E1 =IF(COUNTIF(D$1:D***,D1)1,"Duplicated","") where *** is the last entry in Column D Thanks for looking and if anyone has a slicker way of doing it, please reply. Toney "Toney" wrote: Hi all, I have a list of files that I'd like to mark the duplicates. I can't use just the file name because some files have the same name but aren't the same file. As I understand it COUNTIF can't be used with multiple comparisons. I've tried CONCATENATE several of the columns and then using COUNTIF but it doesn't seem to work. Example of what I'm looking for.... File A, 400k, 9/23/09, Duplicate File A, 450k, 9/23/09 File A, 400k, 9/23/09, Duplicate Thanks in advance Toney |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Marking duplicate entries based on multiple columns
With the SUMPRODUCT() formula applied in D1 and copied down...you dont need
to have a temporary column... If this post helps click Yes --------------- Jacob Skaria "Toney" wrote: DOH! I didn't carry the function down the entire column. Once I did, using CONCATENATE and COUNTIF worked. Well if anyone else needs this info what I did was.... In D1 =CONCATENATE(A1,B1,C1) where A1 is name, B1 is size, C1 is date. In E1 =IF(COUNTIF(D$1:D***,D1)1,"Duplicated","") where *** is the last entry in Column D Thanks for looking and if anyone has a slicker way of doing it, please reply. Toney "Toney" wrote: Hi all, I have a list of files that I'd like to mark the duplicates. I can't use just the file name because some files have the same name but aren't the same file. As I understand it COUNTIF can't be used with multiple comparisons. I've tried CONCATENATE several of the columns and then using COUNTIF but it doesn't seem to work. Example of what I'm looking for.... File A, 400k, 9/23/09, Duplicate File A, 450k, 9/23/09 File A, 400k, 9/23/09, Duplicate Thanks in advance Toney |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Marking duplicate entries based on multiple columns
Jacob,
Thanks for the reply. Your method worked. Toney "Jacob Skaria" wrote: With the SUMPRODUCT() formula applied in D1 and copied down...you dont need to have a temporary column... If this post helps click Yes --------------- Jacob Skaria "Toney" wrote: DOH! I didn't carry the function down the entire column. Once I did, using CONCATENATE and COUNTIF worked. Well if anyone else needs this info what I did was.... In D1 =CONCATENATE(A1,B1,C1) where A1 is name, B1 is size, C1 is date. In E1 =IF(COUNTIF(D$1:D***,D1)1,"Duplicated","") where *** is the last entry in Column D Thanks for looking and if anyone has a slicker way of doing it, please reply. Toney "Toney" wrote: Hi all, I have a list of files that I'd like to mark the duplicates. I can't use just the file name because some files have the same name but aren't the same file. As I understand it COUNTIF can't be used with multiple comparisons. I've tried CONCATENATE several of the columns and then using COUNTIF but it doesn't seem to work. Example of what I'm looking for.... File A, 400k, 9/23/09, Duplicate File A, 450k, 9/23/09 File A, 400k, 9/23/09, Duplicate Thanks in advance Toney |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Marking duplicate entries based on multiple columns
FYI to anyone looking at these answers.
If you're using large amounts of data like I am for this project (25,809 rows) both solutions take a very very long time. This is not something you want to if you're in a hurry. Toney |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Indicate Duplicate Entries Across Multiple Columns | Excel Discussion (Misc queries) | |||
| Duplicate entries in multiple worksheets | Excel Worksheet Functions | |||
| Checking for duplicate entries over multiple worksheets | Excel Discussion (Misc queries) | |||
| count duplicate (or, inversely, unique) entries, but based on a condition | Excel Worksheet Functions | |||
| Multiple Files, Duplicate Entries | Excel Discussion (Misc queries) | |||
Linear Mode
