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.

[TABLE="width: 480"]
<tbody>[TR]
[TD="align: center"]ID
[/TD]
[TD="align: center"]X_Pos
[/TD]
[TD="align: center"]Y_Pos
[/TD]
[TD="align: center"]Intersect?
[/TD]
[TD="align: center"]Duplicate?
[/TD]
[TD="align: center"]Additional Intersect?
[/TD]
[/TR]
[TR]
[TD="align: center"]210181350
[/TD]
[TD="align: center"]324437
[/TD]
[TD="align: center"]180343
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]Y
[/TD]
[/TR]
[TR]
[TD="align: center"]210181350
[/TD]
[TD="align: center"]324437
[/TD]
[TD="align: center"]180343
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]N
[/TD]
[/TR]
</tbody>[/TABLE]



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)
[TABLE="width: 480"]
<tbody>[TR]
[TD="align: center"]ID
[/TD]
[TD="align: center"]X_Pos
[/TD]
[TD="align: center"]Y_Pos
[/TD]
[TD="align: center"]Intersect?
[/TD]
[TD="align: center"]Duplicate?
[/TD]
[TD="align: center"]Additional Intersect?
[/TD]
[/TR]
[TR]
[TD="align: center"]362220943
[/TD]
[TD="align: center"]356634
[/TD]
[TD="align: center"]172646
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]Y
[/TD]
[/TR]
[TR]
[TD="align: center"]362220943
[/TD]
[TD="align: center"]356634
[/TD]
[TD="align: center"]172646
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]Y
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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:[TABLE="class: cms_table, width: 480"]
<tbody>[TR]
[TD="align: center"]
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]X_Pos
[/TD]
[TD]Y_Pos
[/TD]
[TD]Intersect
[/TD]
[TD]Duplicate
[/TD]
[TD]Additional Intersect (Result)
[/TD]
[TD]Example
[/TD]
[/TR]
[TR]
[TD]210181350
[/TD]
[TD]324437
[/TD]
[TD]180343
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]Original
[/TD]
[/TR]
[TR]
[TD]210181350
[/TD]
[TD]324437
[/TD]
[TD]180343
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]Duplicate but not intersect
[/TD]
[/TR]
[TR]
[TD]210181350
[/TD]
[TD]324101
[/TD]
[TD]180101
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Intersect, duplicate and unique grid ref
[/TD]
[/TR]
[TR]
[TD]210181350
[/TD]
[TD]324122
[/TD]
[TD]180122
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Intersect, duplicate and unique grid ref
[/TD]
[/TR]
[TR]
[TD]21019999
[/TD]
[TD]324999
[/TD]
[TD]180999
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]Intersect but not duplicate
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]

Previous errors explained:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]X_Pos
[/TD]
[TD]Y_Pos
[/TD]
[TD]Intersect
[/TD]
[TD]Duplicate
[/TD]
[TD]Additional Intersect (Expected result)
[/TD]
[TD]Explained
[/TD]
[/TR]
[TR]
[TD]210181350
[/TD]
[TD]324437
[/TD]
[TD]180343
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]No intersect / No Duplicate = N
[/TD]
[/TR]
[TR]
[TD]210181350
[/TD]
[TD]324437
[/TD]
[TD]180343
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Intersect + Duplicate + Unique grid ref = Y
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]362220943
[/TD]
[TD]356634
[/TD]
[TD]172646
[/TD]
[TD][/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD]Intersect is blank = N
[/TD]
[/TR]
[TR]
[TD]362220943
[/TD]
[TD]356634
[/TD]
[TD]172646
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]Still blank in the Intersect = N
[/TD]
[/TR]
</tbody>[/TABLE]

​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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"),""):


[TABLE="class: grid, width: 500, 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]210178447
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 49"]
<tbody>[TR]
[TD]323157
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 49"]
<tbody>[TR]
[TD]368999
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[/TR]
</tbody>[/TABLE]



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

[TABLE="class: grid, width: 500, 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]210178447
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 49"]
<tbody>[TR]
[TD]323157
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 49"]
<tbody>[TR]
[TD]368999
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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