Check for duplicates in 2 columns
 

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

Check for duplicates in 2 columns
 

Think you could try this in say, D1:
=IF(COUNTA(A1:B1)<2,"",IF(SUMPRODUCT((A$1:A1=A1)*( B$1:B1=B1))1,"","x"))
Copy down as far as required to directly* mark all uniques based on col A &
B with an "x".

*Your col C is not required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ber" wrote:
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

Check for duplicates in 2 columns
 

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

Check for duplicates in 2 columns
 

Thanks Max, tried this but it is marking ones I do not want at all. the ones
I want with the X are ones that have duplicate specimin numbers but where
they are duplicate to identify the organism in Col B where it is different
e.g if two specimen numbers are the same then sometimes the organismis the
same but other times the organism is different and its when the organism is
different but with the same number I want these two duplicate numbers
identified, below is the result I got using your formula but it put an X on
ones that were not even duplicate numbers.sorry any other thoughts I'm lost.
Really when testing for the duplicates and got TRUE that it is a duplicate I
need some way of telling the formula to then look at the two organisms in COL
A with this dulilcate number in COL B and tell me if they are different
organisms or the same as I need to analyse the different ones so need to pick
them off

Staph. aureus MRSA MM502468L x
Enterococcus MM502468L x
Staph. aureus MRSA MM502481A x
Enterococcus MM502589K x
Enterococcus MM502589K
Enterococcus MM502601F x
Staph. aureus MRSA MT141828F x
Staph. aureus MRSA MT141832N x
Staph. aureus MRSA MY044041V x


"Max" wrote:

Think you could try this in say, D1:
=IF(COUNTA(A1:B1)<2,"",IF(SUMPRODUCT((A$1:A1=A1)*( B$1:B1=B1))1,"","x"))
Copy down as far as required to directly* mark all uniques based on col A &
B with an "x".

*Your col C is not required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ber" wrote:
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

Check for duplicates in 2 columns
 

Maybe this then, in D1, copied down:
=IF(COUNTIF(B:B,B1)1,IF(SUMPRODUCT((A$1:A$100=A1) *(B$1:B$100=B1))1,"","x"),"")

Adapt the range to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ber" wrote:
Thanks Max, tried this but it is marking ones I do not want at all. the ones
I want with the X are ones that have duplicate specimin numbers but where
they are duplicate to identify the organism in Col B where it is different
e.g if two specimen numbers are the same then sometimes the organismis the
same but other times the organism is different and its when the organism is
different but with the same number I want these two duplicate numbers
identified, below is the result I got using your formula but it put an X on
ones that were not even duplicate numbers.sorry any other thoughts I'm lost.
Really when testing for the duplicates and got TRUE that it is a duplicate I
need some way of telling the formula to then look at the two organisms in COL
A with this dulilcate number in COL B and tell me if they are different
organisms or the same as I need to analyse the different ones so need to pick
them off

Staph. aureus MRSA MM502468L x
Enterococcus MM502468L x
Staph. aureus MRSA MM502481A x
Enterococcus MM502589K x
Enterococcus MM502589K
Enterococcus MM502601F x
Staph. aureus MRSA MT141828F x
Staph. aureus MRSA MT141832N x
Staph. aureus MRSA MY044041V x

Check for duplicates in 2 columns
 

I would use a helper column:

=a1&"|"&b1
and drag down

Then I could use the =countif() against that column.

The | is just a field separator. Use a character that doesn't appear in your
data.

You don't want
A B
test1 test2
test1test2 (empty)
test1t est2

to look like duplicates.




Ber wrote:

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

--

Dave Peterson

Check for duplicates in 2 columns
 

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