Comparing two columns containing grid refs to find duplicates.

Hello,

Brand new problem has arisen and I've spent two days trying to work it out. The problem being I am trying to find duplicates within a single column (col A) (which I have succesfully done) once I have found the duplicates I am then trying to work out if grid references attached to the ID in col A are duplicated themselves.

For example ID 12345678 is repeated three times due to 3 intersects against water however 2 intersects are close together that they share the same grid references meanwhile the third intersect has its only unique grid reference due to distance.

Here is an extract from my spreadsheet in non formula view:

[TABLE="class: grid, width: 1000, align: center"]

[tr]

[td]

[TABLE="width: 77"]

[tr]

[td]

ID

[/td]

[/tr]

[/TABLE]

[/td]

[td]

X

[/td]

[td]

Y

[/td]

[td]

Con

[/td]

[td]

Lookup

[/td]

[td]

Pon Lookup

[/td]

[td]

Checked

[/td]

[td]

Intersect

[/td]

[td]

Completed entry?

[/td]

[td]

Duplicate

[/td]

[td]

Addiotional intersect?

[/td]

[/tr]

[tr]

[td]

[TABLE="width: 77"]

[tr]

[td]

363964822

[/td]

[/tr]

[/TABLE]

[/td]

[td]

151815

[/td]

[td]

[TABLE="width: 49"]

[tr]

[td]

32546

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

3639648221518132545

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

#N/A

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

363964822

[/td]

[/tr]

[/TABLE]

[/td]

[td]

Y

[/td]

[td]

Y

[/td]

[td]

Y

[/td]

[td]

N

[/td]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 77"]

[tr]

[td]

363611803

[/td]

[/tr]

[/TABLE]

[/td]

[td]

158801

[/td]

[td]

33112

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

[TABLE="width: 151"]

[tr]

[td]

3636118031588037559

[/td]

[/tr]

[/TABLE]

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

[TABLE="width: 151"]

[tr]

[td]

3636118031588037559

[/td]

[/tr]

[/TABLE]

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

[TABLE="width: 151"]

[tr]

[td]

363611803

[/td]

[/tr]

[/TABLE]

[/td]

[/tr]

[/TABLE]

[/td]

[td]

Y

[/td]

[td]

Y

[/td]

[td]

N

[/td]

[td]

N

[/td]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 77"]

[tr]

[td]

363611805

[/td]

[/tr]

[/TABLE]

[/td]

[td]

161140

[/td]

[td]

[TABLE="width: 49"]

[tr]

[td]

39994

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

[TABLE="width: 151"]

[tr]

[td]

3636118051611439994

[/td]

[/tr]

[/TABLE]

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

[TABLE="width: 151"]

[tr]

[td]

3636118051611439994

[/td]

[/tr]

[/TABLE]

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

[TABLE="width: 151"]

[tr]

[td]

363611805

[/td]

[/tr]

[/TABLE]

[/td]

[/tr]

[/TABLE]

[/td]

[td]

Y

[/td]

[td]

Y

[/td]

[td]

Y

[/td]

[td]

N

[/td]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 77"]

[tr]

[td]

363787342

[/td]

[/tr]

[/TABLE]

[/td]

[td]

162306

[/td]

[td]

[TABLE="width: 49"]

[tr]

[td]

[TABLE="width: 49"]

[tr]

[td]

34007

[/td]

[/tr]

[/TABLE]

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

[TABLE="width: 151"]

[tr]

[td]

3637873421623034006

[/td]

[/tr]

[/TABLE]

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

#N/A

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

363787342

[/td]

[/tr]

[/TABLE]

[/td]

[td]

Y

[/td]

[td]

Y

[/td]

[td]

N

[/td]

[td]

N

[/td]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 77"]

[tr]

[td]

363611789

[/td]

[/tr]

[/TABLE]

[/td]

[td]

165700

[/td]

[td]

[TABLE="width: 49"]

[tr]

[td]

[TABLE="width: 49"]

[tr]

[td]

41697

[/td]

[/tr]

[/TABLE]

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

[TABLE="width: 151"]

[tr]

[td]

[TABLE="width: 151"]

[tr]

[td]

3636117891656941697

[/td]

[/tr]

[/TABLE]

[/td]

[/tr]

[/TABLE]

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

#N/A

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

363611789

[/td]

[/tr]

[/TABLE]

[/td]

[td]

Y

[/td]

[td]

Y

[/td]

[td]

Y

[/td]

[td]

N

[/td]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 77"]

[tr]

[td]

363976725

[/td]

[/tr]

[/TABLE]

[/td]

[td]

167048

[/td]

[td]

[TABLE="width: 49"]

[tr]

[td]

30340

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

[TABLE="width: 151"]

[tr]

[td]

[TABLE="width: 151"]

[tr]

[td]

3639767251670430340

[/td]

[/tr]

[/TABLE]

[/td]

[/tr]

[/TABLE]

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

3639767251670430340

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

363976725

[/td]

[/tr]

[/TABLE]

[/td]

[td]

Y

[/td]

[td]

Y

[/td]

[td]

N

[/td]

[td]

N

[/td]

[td][/td]

[/tr]

[tr]

[td]

[TABLE="width: 77"]

[tr]

[td]

363611789

[/td]

[/tr]

[/TABLE]

[/td]

[td]

166502

[/td]

[td]

[TABLE="width: 49"]

[tr]

[td]

42025

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

3636117891665042025

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

#N/A

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

363611789

[/td]

[/tr]

[/TABLE]

[/td]

[td]

Y

[/td]

[td]

Y

[/td]

[td]

Copies its duplicated cell so in this case Y

[/td]

[td]

Y

[/td]

[td]

Should be Y

[/td]

[/tr]

[tr]

[td]

[TABLE="width: 77"]

[tr]

[td]

363976725

[/td]

[/tr]

[/TABLE]

[/td]

[td]

167085

[/td]

[td]

[TABLE="width: 49"]

[tr]

[td]

30311

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

3639767251670830310

[/td]

[/tr]

[/TABLE]

[/td]

[td]

[TABLE="width: 151"]

[tr]

[td]

#N/A

[/td]

[/tr]

[/TABLE]

[/td]

[td]

363976725

[/td]

[td]

Y

[/td]

[td]

Y

[/td]

[td]

Copies its duplicated cell so in this case Y

[/td]

[td]

Y

[/td]

[td]

Should be Y

[/td]

[/tr]

[/TABLE]

Formula view:

[TABLE="class: grid, width: 1300, align: center"]

[tr]

[td]

ID

[/td]

[td]

X

[/td]

[td]

Y

[/td]

[td]

Con

[/td]

[td]

Lookup

[/td]

[td]

Pon Lookup

[/td]

[td]

Checked

[/td]

[td]

Intersect

[/td]

[td]

Completed entry?

[/td]

[td]

Duplicate

[/td]

[td]

Addtional intersect

[/td]

[/tr]

[tr]

[td]

<number>

[/td]

[td]

<xref>

[/td]

[td]

<yref>

[/td]

[td]

=CONCATENATE(A2,IF(LEN(B2=6),LEFT(B2,5),LEFT(B2,4)),IF(LEN(C2=6),LEFT(C2,5),LEFT(C2,4)))

[/td]

[td]

=VLOOKUP(D2,'below 7 form extract'!K:K,1,0)

[/td]

[td]

=VLOOKUP(A2,'below 7 form extract'!A:A,1,0)

[/td]

[td]

<Y or N>

[/td]

[td]

<Y or N>

[/td]

[td]

=IF(AND(G2="N",ISBLANK(H2)),"N/A",IF(H:H="N","-",IF(ISERROR(F2),"N","Y")))

[/td]

[td]

=COUNTIF($A$2:A2,A2)>1 using conditional formatting and manually typed Y where highlighted.

[/td]

[td][/td]

[/tr]

[/TABLE]

I'll explain the solution that I think would work (I just cannot find a correct solution that works) - IF Duplicate THEN IF Duplicate references take 1st duplicate reference and mark it as Y in additional intersect. If > 2 duplicate references then the 1st is marked as Y and the 2nd is marked as N as so on..

If this is possible then I am all ears in the meantime I shall attempt to find a solution and will post updates if applicable.

Version of Excel: 2010. VBA Newbie: Yes.

Re: Comparing two columns containing grid refs to find duplicates.

As requested I have attached a snippet of my spreadsheet (There are 21005 records in total :))

To explain it a little bit better I have coloured the duplicates based on the IDs. If you look at Row 23 + 24 they are duplicates of Row 22 however all three have unique grid references. In some cases there may more duplicates however some will share grid references. Therefore I need a way of determing which have unique grid references and which don't. The ones that don't get marked into the database if only an intersect has occured and all the ones sharing the grid reference get added under it.

If you want it explaining even further I shall try my best.

Colour Keys - Yellow = Completed, Green = In Progress, Blue = Duplicates, Red = No Intersect.

Thanks in advance!.

Re: Comparing two columns containing grid refs to find duplicates.

Spent another day trying to figure this out to no luck. I reckon it can be done using VBA just cannot work out where to start with VBA to explore possible solutions...

Re: Comparing two columns containing grid refs to find duplicates.

In order to seek more help on this I have now asked the folk at MrExcel for help as well (Link - http://www.mrexcel.com/forum/e…refs-find-duplicates.html)