I have list with 5 columns. I am trying to identify duplicates in rows and
columns can this be done in Excel? or How should I go about this? For
instance
col contains id and need to check to see if there are dups. For the first
col(ids) I am using this function =IF(COUNT(B$2:B2,B2)1,"Duplicate","No
Dup") this works fine. However, going across the next three rows contain the
same information but col 5 in most instances contain the same information for
the rows but there are some instances where the value may be different. I
want to be able to flag/identify these exceptions. Can there be something
added to the function above to include up to col 5 for "Duplicates". Can
someone please help me with this function or what is a better what to
identify "duplicates" down and across this worksheet??? Thank you in advance
for any help you can provide.

hi, anonymous !

not so sure i'm following you correctly -?-

you might want to give a try to something like this:

=sumproduct(--((a$2:a2&b$2:b2&c$2:c2&d$2:d2&e$2:e2)=(a2&b2&c2&d2 &e2)))1

to see the number of dupes, just remove the final "1"

hth,
hector.

__ OP __
I have list with 5 columns.
I am trying to identify duplicates in rows and columns can this be done in Excel? or How should I go about this?
For instance col contains id and need to check to see if there are dups.
For the first col(ids) I am using this function =IF(COUNT(B$2:B2,B2)1,"Duplicate","No Dup") this works fine.
However, going across the next three rows contain the same information
but col 5 in most instances contain the same information for the rows but there are some instances where the value may be different.
I want to be able to flag/identify these exceptions.
Can there be something added to the function above to include up to col 5 for "Duplicates".
Can someone please help me with this function or what is a better what to identify "duplicates" down and across this worksheet???
Thank you in advance for any help you can provide.

One simple way is to use a helper col to concat the 5 source cols, then to
flag it based on that helper col

Source data assumed in cols A to E (the 5 cols), from row2 down
In F2:
=TRIM(A2)&"#"&TRIM(B2)&"#"&TRIM(C2)&"#"&TRIM(D2)&" #"&TRIM(E2)

In G2:
=IF(COUNTIF(F$2:F2,F2)1,"Dup","")
Copy F2:G2 down to the last row of source data
Col G will flag the duplicates as Dup
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---
"TotallyConfused" wrote:
I have list with 5 columns. I am trying to identify duplicates in rows and
columns can this be done in Excel? or How should I go about this? For
instance
col contains id and need to check to see if there are dups. For the first
col(ids) I am using this function =IF(COUNT(B$2:B2,B2)1,"Duplicate","No
Dup") this works fine. However, going across the next three rows contain the
same information but col 5 in most instances contain the same information for
the rows but there are some instances where the value may be different. I
want to be able to flag/identify these exceptions. Can there be something
added to the function above to include up to col 5 for "Duplicates". Can
someone please help me with this function or what is a better what to
identify "duplicates" down and across this worksheet??? Thank you in advance
for any help you can provide.