Excel Function???

[TotallyConfused]

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.

 

[Héctor Miguel]

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 __

 

[Max]

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