Duplicates

[Steve]

Hi, is it possible to identify duplicates when looking in tow
different columsn.

An example is as follows:



Time (Column B) Name (Column M) Duplicate (Column JB)


2:40:00 Colin 1 2:40:00
Colin 1
2:40:00 Brian 1
2:40:00 Colin 1
2:40:00 Brian 1
2:40:00 John 0
2:40:00 Simon 0
2:40:00 Graeme 0
3:10:00 David 1
3:10:00 Ronald 0
3:10:00 David 1
3:10:00 Gordon 0
3:10:00 Frank 0
3:10:00 Colin 0


So in essence I would like a fomula that looks at Column A and Column
B and where there is more than one match populates Column JB with a 1,
if duplicates found or 0 if only one entry for that particualr time.

Is that possible?


Any help apreciated

Regards


Steve

 

[Claus Busch]

Hi Steve,

Am Thu, 25 Apr 2013 15:38:47 +0100 schrieb Steve:

Time (Column B) Name (Column M) Duplicate (Column JB)

2:40:00 Colin 1 2:40:00
Colin 1
2:40:00 Brian 1
2:40:00 Colin 1
2:40:00 Brian 1
2:40:00 John 0
2:40:00 Simon 0
2:40:00 Graeme 0
3:10:00 David 1
3:10:00 Ronald 0
3:10:00 David 1
3:10:00 Gordon 0
3:10:00 Frank 0
3:10:00 Colin 0

So in essence I would like a fomula that looks at Column A and Column
B and where there is more than one match populates Column JB with a 1,
if duplicates found or 0 if only one entry for that particualr time.

try in JB1:
=--(SUMPRODUCT(--($B$1:$B$100=B1),--($M$1:$M$100=M1))>1)
and copy down


Regards
Claus Busch

 

[Steve]

Hi Claus and thansk for the quick reply as always.

I have tried the formula but it is returning 1s where there are no
duplicates and I cant see why !

The name in column M is not jsut as shown int he example it is liek
this

Jones, AJ
Johnston, M

Would that make a difference?

Thanks again



Steve

 

[Claus Busch]

Hi Steve,

Am Thu, 25 Apr 2013 16:47:54 +0100 schrieb Steve:

The name in column M is not jsut as shown int he example it is liek
this

Jones, AJ
Johnston, M

I think you have some blanks in your column
Click in column header => TextToColumns => Fixed width => Finish.
If that is not the solution, send me the file


Regards
Claus Busch

 

[Steve]

Sorry Claus you were spot on I had made a mistake when I was adjusting
it for my spreadsheet - thanks

Kind regards

Steve