Comparing two columns containing grid refs to find duplicates.

[squirrel31]

Hello,

First off I'd like to point out that I originally had posted this query at the following link:- Comparing two columns containing grid refs to find duplicates. 5 days ago where I was unsucessful in gaining any replies that helped me out. (You will also find an attached example showing a snippet of my work so far)

As stated in the thread using the above link. I have a problem where I have in one column I have an ID, to the right I have Pos_X, to the right of that I have Pos_Y (Grid reference coords).

I have used a formula to find the duplicate IDs so that I could filter them out however I now need to analyse these duplicates further. Therefore I need to work out within the duplicate IDs which have duplicate grid references and which have unique grid references. Only the first of x amount of duplicate grid refs is then marked down in the Additional intersect? column using the format 'Y'. Any unique grid ref ID is also marked down as 'Y' in the Additional intersect? column.

Any scenario where a 'N' is marked would be the following:

• The ID is not a duplicate therefore there is only one position for that ID.
• There are multiple duplicate grid references and therefore the 2nd onwards are marked as N whilst the first is Y.

Any scenario where a 'Y' is marked are as follows:

• There is a duplicate grid reference under the same ID which only the first duplicate grid ref is marked as Y
• There are x amount of unique grid references under the same ID which all are marked as Y

Thanks in advance!

 

[steve the fish]

To be clear do all 3 columns you mention have to be the same to be classed as a duplicate? Some sample data with expected results would be good

As stated in the thread using the above link. I have a problem where I have in one column I have an ID, to the right I have Pos_X, to the right of that I have Pos_Y (Grid reference coords).

 

[Andrew Poulsom]

Can't you use a formula like?

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)>1,"Y","N")

 

[squirrel31]

To be classified as a duplicate the following needs to be true:

• ID is same

To be Y the following needs to be true:

• ID is same
• Pos_X and Pos_Y are unique

To be N the following needs to be true:

• ID is same
• Pos_X and Pos_Y are the same

As for that formula Andrew it doesn't quite work in the way I want (See the example I posted in the original thread I mentioned). It is currently telling me that there is an additional intersect at a non-duplicate record. Additionally, it is telling me there is an additional intersect for a record that is a duplicate however the grid refs are the same in both.

 

[Andrew Poulsom]

Please post some sample data including examples where the formula I suggested isn't returning the expected result and the reason. We need to keep everything in this thread rather than referring to one in another Forum.

 

[squirrel31]

Example in table form (Additional Intersect shows expected results):

Orange = Completed
Green = In progress
Red = No intersect
Violet = Duplicate

[TABLE="class: grid, width: 750, align: center"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Pos_X
[/TD]
[TD]Pos_Y
[/TD]
[TD]Con
[/TD]
[TD]Lookup
[/TD]
[TD]Pon Lookup
[/TD]
[TD]Checked?
[/TD]
[TD]Intersect?
[/TD]
[TD]Completed Entry?
[/TD]
[TD]Duplicate?
[/TD]
[TD]Additional Intersect?
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]363964822
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]151815
[/TD]
[TD]32546
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]3639648221518132545
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]363964822
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]363611803
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]158801
[/TD]
[TD]37559
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]3636118031588037559
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]3636118031588037559
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]363611803
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]363611805
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]161140
[/TD]
[TD]39994
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]3636118051611439994
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]3636118051611439994
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]363611805
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]363976725
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]167048
[/TD]
[TD]30340
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]3639767251670430340
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]3639767251670430340
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]363976725
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]363611789
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]166502
[/TD]
[TD]42025
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]3636117891665042025
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]363611789
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]363976725
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]167085
[/TD]
[TD]30311
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]3639767251670830310
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]363976725
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]630624513
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]178576
[/TD]
[TD]43780
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]6306245131785743780
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]630624513
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]210182335
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD]212551
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]200431
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]2101823352125520043
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]210182335
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]-
[/TD]
[TD]N
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]210182301
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]242047
[/TD]
[TD]205823
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]2101823012420420582
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]210182301
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]210182310
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD]241135
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]205965
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]2101823102411320596
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]210182310
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]210182300
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD]242677
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]205715
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]2101823002426720571
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]210182300
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]210182300
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD]243785
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]205306
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]2101823002437820530
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]210182300
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD]123456
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]234567
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]11234523456
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 71"]
<tbody>[TR]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 64"]
<tbody>[TR]
[TD]123456
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]234567
[/TD]
[TD]
[TABLE="width: 143"]
<tbody>[TR]
[TD]11234523456
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[/TR]
</tbody>[/TABLE]

Results that went wrong (Additional Intersect is current using formula):

210182109, 210182132 and 1000534854 are wrong because they are not duplicates. 363631355 is wrong because although they are duplicates (Same ID) the grid references are the same therefore no unique grid refs)

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Pos_X
[/TD]
[TD]Pos_Y
[/TD]
[TD]Con
[/TD]
[TD]Lookup
[/TD]
[TD]Pon Lookup
[/TD]
[TD]Checked
[/TD]
[TD]Intersect
[/TD]
[TD]Completed Entry?
[/TD]
[TD]Duplicate
[/TD]
[TD]Additional Intersect?
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 77"]
<tbody>[TR]
[TD]210182109
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 49"]
<tbody>[TR]
[TD]261133
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 49"]
<tbody>[TR]
[TD]193517
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]2101821092611319351
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 77"]
<tbody>[TR]
[TD]210182132
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 49"]
<tbody>[TR]
[TD]266185
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 49"]
<tbody>[TR]
[TD]193035
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]2101821322661819303
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]-
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 77"]
<tbody>[TR]
[TD]363631355
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 49"]
<tbody>[TR]
[TD]255745
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 49"]
<tbody>[TR]
[TD]133052
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]3636313552557413305
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 59"]
<tbody>[TR]
[TD]Y
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]N
[/TD]
[TD]-
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 77"]
<tbody>[TR]
[TD]363631355
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 49"]
<tbody>[TR]
[TD]255745
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 49"]
<tbody>[TR]
[TD]133052
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]3636313552557413305
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 59"]
<tbody>[TR]
[TD]Y
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]N
[/TD]
[TD]-
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 750, align: center"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Pos_X
[/TD]
[TD]Pos_Y
[/TD]
[TD]Con
[/TD]
[TD]Lookup
[/TD]
[TD]Pon Lookup
[/TD]
[TD]Checked
[/TD]
[TD]Intersect
[/TD]
[TD]Completed Entry?
[/TD]
[TD]Duplicate
[/TD]
[TD]Additional Intersect?
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 77"]
<tbody>[TR]
[TD]1000534854
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 49"]
<tbody>[TR]
[TD]272342
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 49"]
<tbody>[TR]
[TD]192919
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]10005348542723419291
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 151"]
<tbody>[TR]
[TD]1000534854
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[/TR]
</tbody>[/TABLE]

 

[squirrel31]

Hi,

I've just retried =IF(COUNTIFS(A$2:A2,A1,B$2:B2,B1,C$2:C2,C2)>1,"Y","N") and for some reason it is now detecting whether or not it is a duplicate however it appears that it is not sensing whether the grid references are unique or not. 1st part correct now just need to fine tune it


Just a few examples where its now going wrong.

Reason why 210182300 should be Y is because it is a duplicate and has a unique grid reference.


[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Pos_X[/TD]
[TD]Pos_Y[/TD]
[TD]Con[/TD]
[TD]Lookup[/TD]
[TD]Pon Lookup[/TD]
[TD]Checked[/TD]
[TD]Intersect[/TD]
[TD]Completed Entry?[/TD]
[TD]Duplicate[/TD]
[TD]Additional Intersect?[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD]363631360[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD]255239[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD]133365[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 151"]
<tbody>[TR]
[TD]3636313602552313336[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 151"]
<tbody>[TR]
[TD]#N/A[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 151"]
<tbody>[TR]
[TD]1000546862[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD]Y[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD]Y[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD]Y[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD]Y[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD]Y[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD]363631360[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD]255239[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD]133365[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 151"]
<tbody>[TR]
[TD]3636313602552313336[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 151"]
<tbody>[TR]
[TD]#N/A[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 151"]
<tbody>[TR]
[TD]1000546862[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD]Y[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD]Y[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD]Y[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD]Y[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD]Y (SHOULD BE N)[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD]210182300[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD]242677[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD]205715[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 151"]
<tbody>[TR]
[TD]2101823002426720571[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 151"]
<tbody>[TR]
[TD]#N/A[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 151"]
<tbody>[TR]
[TD]210182300[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N (SHOULD BE Y)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

Shouldn't the formula be?

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)>1,"Y","N")

Rather than posting some isolated snippets from your data please create some which clearly demonstrates the results you are expecting.

 

[squirrel31]

Sorry, my excel seems to have the mind of its own. Thought I had copied that formula correctly (even compared them in notepad...)

One example that shows the result I should be expecting:

[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Pos_X[/TD]
[TD]Pos_Y[/TD]
[TD]Intersect?[/TD]
[TD]Duplicate?[/TD]
[TD]Additional Intersect?[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD]210182300[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD]243130[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD]205587[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD]210182300[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD]243130[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD]205587[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD]210182300[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD]242677[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD]205715[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD]210182300[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD]243785[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD]205306[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]


Explained: The second row is the first time 210182300 appears so is not a duplicate. The third row is where an intersect appears however shares the same grid reference meaning it can be ignored. The following two have the same IDs however they both have unique grid references therefore they are additional intersects (because they also intersect).

Rules:

Same ID + Unique grid ref + Intersect is Y = Additional intersect is Y
Same ID + No unique grid ref + Intersect is Y = Additional intersect is N
Same ID + Intersect is N = Additional intersect is -
Same ID + Duplicate is N = Additional intersect is -

I have removed the columns that do not affect what I am trying to produce (and I aplogise for adding them in as looking back it over-complicates things here).

I shall try and sum up the process again: There are 21004 records of which there are duplicates (9144). I require to determine which ones out of the 9144 I need to deal with and which I can ignore.

I have revised the method to make it easier (I hope)

To be classified as a non-duplicate the following needs to be true:

• Unique ID
• if x amounts appear of IDs then the first is classified as non-duplicate

To be a duplicate the following needs to be true:

• Same ID
• if x amounts appear of IDs then the 2nd one onwards (2nd,3rd...) are all classified as duplicates.

To be an additional intersect the following needs to be true:

• Record needs to be a duplicate
• Grid ref needs to be unique
• if x amounts of grid refs (needs to be different to the same ID classified as non-duplicate) appear then the first is classified as an additional intersect
• Intersect = Y

 

[Andrew Poulsom]

Maybe:

Excel 2010
	A	B	C	D	E	F
1	ID	Pos_X	Pos_Y	Intersect?	Duplicate?	Additional Intersect?
2	210182300	243130	205587	Y	N
3	210182300	243130	205587	Y	Y	N
4	210182300	242677	205715	Y	Y	Y
5	210182300	243785	205306	Y	Y	Y
Sheet1

Cell Formulas
Range	Formula
E2	=IF(COUNTIF(A$2:A2,A1)>1,"Y","N")
F2	=IF(E2="Y",IF(AND(D2="Y",COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)>1),"N","Y"),"")