Complicated Duplicate Issue

**roasty_1** · 04-23-2008, 11:52 AM

Hi

This is tricky, but I hope there is a way of doing this

I will simplify the problem to help.

I have 2 columns, A and B
A is ID1
B is ID2

How can I work out if there are duplicate ID2s pointing at the same LE ID without doing filters, so basically if there are more than 1 ID2s pointing to 1 ID1?

Another spanner is the works is that an ID1 can have more than 1 ID2.

Mini file attached which should help with my explanation

Kind regards

Keith

**Portuga** · 04-23-2008, 12:06 PM

Hi,

Your file is not attached.

**roasty_1** · 04-24-2008, 04:45 AM

file now attached

thanks

**Portuga** · 04-24-2008, 08:56 AM

Hi,

The obvious choice would be the advanced filter. Choose both columns and then filter by unique entries.
Or

the code bellow will flag duplicate entries in red:
(www.techonthenet.com)

Please Login or Register  to view this content.

**roasty_1** · 04-26-2008, 07:41 PM

Not really what I wanted, but thanks, it is much more complicated than an advanced filter

The criteria is as follows

ID1 is linked to one+ ID2's, no single ID2 can be linked to the same ID1 more than once, but an ID2 can be linked to two different ID1's, for example

ID1 ID2 RESULT
1 A DUPLICATE
1 A DUPLICATE
1 A DUPLICATE
2 B NOT A DUPLICATE
2 C NOT A DUPLICATE
3 C NOT A DUPLICATE
4 D DUPLICATE
4 D DUPLICATE

Is there there away to automate this as I have a huge data set to apply this to so manual checks is out of the question

If anyone can help please help

**roasty_1** · 04-27-2008, 03:01 PM

Hi Portuga

Thanks for the code, it works spot on, I didnt quite get it right from you reply but it I managed to get it right. Thanks again, great help.

Keith

**Teethless mama** · 04-27-2008, 03:54 PM

Originally Posted by roasty_1

file now attached

thanks

=IF(AND(COUNTIF($A$2:$A$27,A2)>1,COUNTIF($B$2:$B$27,B2)>1),"dup","")

**roasty_1** · 05-02-2008, 05:52 AM

Originally Posted by Portuga

Hi,

The obvious choice would be the advanced filter. Choose both columns and then filter by unique entries.
Or

the code bellow will flag duplicate entries in red:
(www.techonthenet.com)

Please Login or Register  to view this content.

This code works spot on, however I need to run this code over 30000 rows, it takes hours to calculate. Is there a way to split the code so it only highlights the 1st 5000 cells that are duplicates but still looking at the whole range?

Thanks