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]

Don't you understand the formula? Just adjust its logic so that you get the result you want.

 

[squirrel31]

Considering the fact I didn't really understand the formula as I wrote it I spent the night researching and I believe I know understand it fully. Therefore this is the new revised formula that appears to do the top:

=IF(AND(L16="Y",N16="Y"),IF(COUNTIFS(J$14:J16,J16,K$14:K16,K16)>1,"N","Y"),"")

I will begin testing it against my 21005 records but so far so good!