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]

I mean why is the first occurrence of 210181350 N rather than blank?

Try to spell out the conditions for each of N, Y and blank.

 

[squirrel31]

It should have been blank, my bad sorry.

Conditions:

For N:

• Not Intersect + Duplicate
• Intersect + Not Duplicate

For Y:

• Intersect + Duplicate + Unique Grid Ref

For Blank:

• Not Intersect + Not Duplicate

 

[Andrew Poulsom]

The logic for that is:

=IF(AND(D2<>"Y",E2<>"Y"),"",IF(AND(D2="Y",E2="Y"),"Y","N"))

 

[squirrel31]

Interesting. Now how would I go about merging that with this formula: =IF(J1324="Y",IF(AND(H1324="Y",COUNTIFS(A$2:A1324,A1324,B$2:B1324,B1324,C$2:C1324,C1324)>1),"N","Y"),"") so that I can determine the ones with unique grid refs?

 

[Andrew Poulsom]

Given the results of columns D:F what is the condition for unique grid ref?

 

[squirrel31]

Unique grid ref:

• Must be duplicate
• Must intersect
• Must be same ID to its original ID
• Must be unique against any grid reference
• An unique grid ref = Additional Intersect equals Y only if duplicate equals Y.

 

[Andrew Poulsom]

What does this mean exactly?

Must be unique against any grid reference

Give the logic in terms of range references.

 

[squirrel31]

Every ID has its own grid reference. However, due to the close proximity some IDs share grid references. Pos_X is located in Column B and Pos_Y is located in Column C. There are a total of 21004 records (B2:B21005, C2:C21005). Unique grid references can either be ones that are duplicates or non-duplicates. I am trying to find a way that will filter out the non-unique grid references so that I only have to manually check the remaining ones (A.k.a the Y in Additional Intersect)

 

[Andrew Poulsom]

I am struggling to understand how a duplicate can be unique. By definition something that's unique is the only one of its kind.

 

[squirrel31]

My definition of duplicate: Where an ID is repeated (Duplicate). These repeated IDs can sometimes have multiple grid references linked to them. For example ID - 123456 can appear three times with each time it appears a unique grid reference (i.e. each 123456 has its own x_Pos and y_Pos).

Example:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]x_Pos
[/TD]
[TD]y_Pos
[/TD]
[TD]Duplicate?
[/TD]
[TD]Unique? (Only time the grid ref appears linked to the ID)
[/TD]
[TD]Intersect?
[/TD]
[TD]Additional Intersect?
[/TD]
[/TR]
[TR]
[TD]123456
[/TD]
[TD]201
[/TD]
[TD]201
[/TD]
[TD]N (First time 123456 appears in the records)
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N (Not a duplicate)
[/TD]
[/TR]
[TR]
[TD]123456
[/TD]
[TD]101
[/TD]
[TD]101
[/TD]
[TD]Y (2nd time it appears in the records)
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y (It a duplicate and intersects)
[/TD]
[/TR]
[TR]
[TD]123456
[/TD]
[TD]301
[/TD]
[TD]301
[/TD]
[TD]Y (3rd time the same ID appears in the records
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]123456
[/TD]
[TD]301
[/TD]
[TD]301
[/TD]
[TD]Y
[/TD]
[TD]N (Grid ref appears before therefore not unique)
[/TD]
[TD]Y
[/TD]
[TD]N (Not unique / ignored)
[/TD]
[/TR]
</tbody>[/TABLE]