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!

 

[Andrew Poulsom]

It seems that Unique can be determined with just:

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

In words, it's not the first occurrence of the ABC combination.

 

[squirrel31]

The attached file shows it doesn't quite work (plus expected results + why)

TinyUpload.com - best file hosting solution, with no limits, totaly free

 

[Andrew Poulsom]

The logic there would be simply:

=COUNTIF(I$14:I14,I14)>1

 

[squirrel31]

Am I right in saying thats the logic for determing whether or not the ID is a duplicate or not?

Additionally, may you take a look at this and tell me if I've succeeded in determining whether its an additional intersect based on the previous conditions? I think I've broken excel in order to potentially have done it..

TinyUpload.com - best file hosting solution, with no limits, totaly free

 

[Andrew Poulsom]

The formula returns TRUE if the entry in column A isn't first occurrence.

 

[squirrel31]

Just realised that this formula is probably just lucky to be working to an extent. However, if i make two IDs the same and make both Pos_X and Pos_Y the same it still believes that it would be an additional intersect despite one only being the additional intersect and the other the one that can be ignored.

Formula: =IF(AND(L14="Y",N14="Y"),IF(COUNTIFS(J$14:J$14,J14,K$14:K$14,K14),IF(COUNTIF(I$14:I14,I14)>1,"Y","N"),"Y"),"N")

Problem:

[TABLE="width: 481"]
<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]363976725
[/TD]
[TD]167048
[/TD]
[TD]30311
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]363976725
[/TD]
[TD]167048
[/TD]
[TD]30311
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

Use the Evaluate Formula tool to step through the formula. Is it working the way you want?

As an aside you have some unnecessary $ signs here (highlighted in red):

COUNTIFS(J$14:J$14,J14,K$14:K$14,K14)

 

[squirrel31]

Using the evalutate formula tool the bits that appear to be working are when it looks to see if there is an intersect and whether its duplicte. However, it doesn't appear to be detecting whether the grid reference is the first occurance or not.

 

[Andrew Poulsom]

It looks like you are missing a >1 after COUNTIFS.

 

[squirrel31]

Added the missing >1 however it did not change the outcome :/

Noticed if I make this change - =IF(AND(L16="Y",N16="Y"),IF(COUNTIFS(J$14:J16,J16,K$14:K16,K16),IF(COUNTIF(I$14:I16,I16)>1,"Y","N"),"N"),"N") I get this (Very very close to the outcome I want - In the Additional Intersect column I require the N's to be Y's and vice versa to achieve my expected results):
[TABLE="width: 535"]
<tbody>[TR]
[TD="colspan: 2, align: center"][/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Intersect[/TD]
[TD="align: center"]Duplicate?[/TD]
[TD="align: center"]Additional Intersect?[/TD]
[/TR]
[TR]
[TD="colspan: 2, align: center"]Duplicate 1 ->[/TD]
[TD="align: center"]363611789[/TD]
[TD="align: center"]166502[/TD]
[TD="align: center"]42025[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="colspan: 2, align: center"]Duplicate 2 ->[/TD]
[TD="align: center"]363976725[/TD]
[TD="align: center"]167048[/TD]
[TD="align: center"]30311[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="colspan: 2, align: center"]Duplicate 2.1 ->[/TD]
[TD="align: center"]363976725[/TD]
[TD="align: center"]167048[/TD]
[TD="align: center"]30311[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="colspan: 2, align: center"]Duplicate 2.2 ->[/TD]
[TD="align: center"]363976725[/TD]
[TD="align: center"]167080[/TD]
[TD="align: center"]167080[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[/TR]
</tbody><colgroup><col span="2"><col><col span="2"><col><col><col span="2"></colgroup>[/TABLE]