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!

 

[squirrel31]

Small fix to your E2 formula - =IF(COUNTIF(A$2:A2,A2)>1,"Y","N").

Just implementing that formula into my spreadsheet now. Can confirm that bit works. Will post if I have problems with F2 in a moment or so.

 

[squirrel31]

One problem I am noticing is that if D = N then it will still think that its an additional intersect when its not actually intersecting at all.

 

[Andrew Poulsom]

Examples please.

 

[squirrel31]

Example of problem where D = N and it thinks it is an additional intersect (If you change D to Y it will now think it isn't an additional intersect.

ID	X_Pos	Y_Pos	Intersect?	Duplicate?	Additional Intersect?
210181350	324437	180343	N	N	Y
210181350	324437	180343	Y	N	N

<tbody>
</tbody>

Example of problem where D = empty (unchecked record) and it thinks it is an additional intersect (Also in this case 1 should be Additional intersect and the other not)

ID	X_Pos	Y_Pos	Intersect?	Duplicate?	Additional Intersect?
362220943	356634	172646		Y	Y
362220943	356634	172646		Y	Y

<tbody>
</tbody>

 

[Andrew Poulsom]

So what should the results be and why? You just need to add some more logic to the formula.

 

[squirrel31]

Sorry for the delayed response.

Looking at the formula I believe it is saying this: If its a duplicate go through the rest of the forumula else leave the cell blank. The middle part appears to be saying If its an intersect and if there are mutiple grid references mark it N else mark it Y.

Applying logic to this it should in theory be working as that is the process which I am looking for. However, I believe the problem might be in the main segment of the formula where it looks at finding it there are multiple grid references. I'm no expert (hence the original ask for help) but could there be a way to split the main segment (COUNTIFS(A$2:A56,A56,B$2:B56,B56,C$2:C56,C56)>1),"N","Y") into two parts. The first finding duplicate IDs and the 2nd part (only occuring if duplicate IDs) finds duplicate grid references.

Therefore. It'll look something like this - If Duplicate then proceed > If Intersect and duplicate ID then proceed > If duplicate grid reference and unique then Y else N.

Logic image attached (TinyUpload.com - best file hosting solution, with no limits, totaly free).

Expected results:

ID X_Pos Y_Pos Intersect Duplicate Additional Intersect (Result) Example 210181350 324437 180343 N N N Original 210181350 324437 180343 N Y N Duplicate but not intersect 210181350 324101 180101 Y Y Y Intersect, duplicate and unique grid ref 210181350 324122 180122 Y Y Y Intersect, duplicate and unique grid ref 21019999 324999 180999 Y N N Intersect but not duplicate <tbody> </tbody>

Previous errors explained: ID X_Pos Y_Pos Intersect Duplicate Additional Intersect (Expected result) Explained 210181350 324437 180343 N N N No intersect / No Duplicate = N 210181350 324437 180343 Y Y Y Intersect + Duplicate + Unique grid ref = Y 362220943 356634 172646 N N Intersect is blank = N 362220943 356634 172646 Y N Still blank in the Intersect = N <tbody> </tbody> ​

<tbody>
</tbody>

 

[Andrew Poulsom]

What happened to the blanks for first occurrence?

 

[squirrel31]

Using =IF(E2="Y",IF(AND(D2="Y",COUNTIFS(A$2:2,2,B$2:2,2,C$2:2,2)>1),"N","Y"),""):

ID	Pos_X	Pos_Y	Intersect	Duplicate	Additional Intersect?
210178447 <tbody> </tbody>	323157 <tbody> </tbody>	368999 <tbody> </tbody>		Y	Y

<tbody>
</tbody>

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

ID	Pos_X	Pos_Y	Intersect	Duplicate	Additional Intersect?
210178447 <tbody> </tbody>	323157 <tbody> </tbody>	368999 <tbody> </tbody>		Y	N

<tbody>
</tbody>

 

[Andrew Poulsom]

Is that an answer to my question?

 

[squirrel31]

By first occurance I thought you meant what was the result of the first formula (sorry if I understood). They are blanks because I have yet to manually check that record yet.

Hopefully that answers it