Thanks All, Rick your formula seems to be the one that works for me, do not
understand what you were telling me Dave thanks anyway

"Rick Rothstein (MVP - VB)" wrote:

Try this formula in D2 and then copy it down...

=IF(SUMPRODUCT(C2*(B$2:B$100=B2)*(A$2:A$100<A2))= 0,"","X")

The TRUEs in Column C where the organisms are different will be marked with
an "X". You will have to examine these lines, though, in case you have 3 or
more TRUES with the same Specimen Number because, then, some could be the
same, while others differed, and all would be marked with an "X".

Rick


"Ber" wrote in message
...
I have a spreadsheet with hundreds of rows and have specimen number
identifying specimens taken but sometimes this is done twice thus creating
a
duplicate and this is not a problem as I have tested for duplicates using
=COUNTIF(B:B,B2)1 and gives me True if the number is repeated but now I
need
to know on the True resuts if the organism relating to the same number is
similar or different e.g A1 and A2 are True but have a different Organism
(Staph. aureus and MRSA
Enterococcus) but A4 and A5 are also True but have the same organism
(Enterococcus and Enterococcus so I m not interested in identifying these
two
I just need the ones identified which are true but the organisms are
different that match the number, hope I've made this clear and someone can
help me as I am manually searchin the TRUES for the different organisms at
the moment and on 600 or more rows its getting hard.
A B
C
Organism Specimen Number DUPLICATE
1 Staph. aureus MRSA MM502468L TRUE
2 Enterococcus MM502468L TRUE
3 Staph. aureus MRSA MM502481A FALSE
4 Enterococcus MM502589K TRUE
5 Enterococcus MM502589K TRUE
6 Enterococcus MM502601F FALSE
7 Staph. aureus MRSA MT141828F FALSE
8 Staph. aureus MRSA MT141832N FALSE
9 Staph. aureus MRSA MY044041V FALSE