Tagging duplicate entries

[TheSlayer3k]

Hi there.
I need some help with a spreadsheet!

I need my spreadsheet to tag duplicate entries. I've attached th
document to make the problem easier to describe (remember kids, alway
virus scan first!!)
On the sheet theres 3 columns of references, if duplicate reference
are entered into any of these cells i would like a message to appear i
the empty cell under each one of the duplicate cells alerting th
user.
I've racked my brains out on this one and can't think of a formulea
can anyone help?

Thanks!

Ben.

Oh yeah, unfortunatly it does HAVE to be a formule

Attachment filename: electronic funds transfer.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=48148

 

[Frank Kabel]

Hi
take look at
http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates
as a starting point

 

[TheSlayer3k]

yeah, i was looking at that before but it doesn't work!

Using that formulea just makes a 0 appear for some reason. Im usin
Excel 2004 if that makes much diffrerence

 

[Frank Kabel]

Hi
I doubt you're using Excel 2004 )) - unless you have a time
machine..

The formulas on this site should do. You may post:
- some example data of your spreadsheet (in plain text - no attachment
please)
- the actual formula you have used

 

[TheSlayer3k]

ok, i'll try a different approach (and i meant excel 2003 ).

im using the formulea =IF(C19=C21,"Duplicate","")

i also want to check C19 against C23, C19 against C25, C19 against C2
etc.
I need to do it all in one forumulea though, any ideas?

Ben

 

[Frank Kabel]

Hi
try
=IF(COUNTIF($C$1:$C$100,C19)>1,"Duplicate","")

as described on the site I provided to you

 

[TheSlayer3k]

lol, it wont work though!

please take a look at the attachment in my first post and try tha
forumulea on it, it just returns a "0"

 

[Frank Kabel]

Hi
normally I won't open attachments
In your case though a formula may be possible this would be quite
complicated as your data is not stored in a contigenous range. So I'd
suggest to change the layout of your spreadsheet to a database oriented
design with to columns and multiple rows. Otherwise all kind of
formulas and calculations will get complicated

 

[TheSlayer3k]

i can't. it HAS to be set out this way unfortunatly. It's such a pain i
the ass :

 

[TheSlayer3k]

is there a way I can use the Or function?

I don't really know how to use it in excel but could it be somethin
like
=IF (c19 = c21 or (c19 = c23 or (c19 = c25 (etc))),"Duplicate","")

probebly not, that was a wild stab but is there anything like that

 

[Dave Peterson]

I haven't followed the complete thread and didn't open your workbook, but how
about something like:

=SUMPRODUCT(--(C19=C21:C99),--(MOD(ROW(C21:C99),2)=1))

=IF(SUMPRODUCT(--(C19=C21:C99),--(MOD(ROW(C21:C99),2)=1))=0,"","duplicate")

It's using the fact that you're checking the odd numbered rows.

 

[TheSlayer3k]

that sort of works for 1 cell, but theres 3 columns of references.

why doesn't anyone like opening attached files??? im no virus badboy :

 

[TheSlayer3k]

i ended up using this

=IF(AND(OR(C19=C21,C19=C23,C19=C25,C19=C27,C19=E19,C19=E21,C19=E23,C19=E25,C19=E27,C19=G19,C19=G21,C19=G23,C19=G25,C19=G27),C19<>""),"Duplicate","")

i'll leave it here for reference to anyone else who needs i