Formula to remove duplicate co-ordinate pairs

[eejwo]

I have a list of x,y co-ordinate pairs (x1,y1 x2,y2) that describe the two end points of vertices for adjoining polygonal cells.

However within this list I have duplicates, as adjoining cells share vertices. Where the co-ordinate pairs are direct duplicates, obviously they are very easy to remove, but the x,y pairs can also be reversed and describe the same vertex. e.g. Vertex "x" for cell "x" maybe described in the list by x1,y1 x2,y2, whilst vertex "x" for cell "y" (i.e. the same vertex) might be in the list as x2,y2 x1,y1, therefore appearing as unique in the list but describing the same vertex.

I want to be left with a list of co-ordinate pairs that don't contain any duplicate vertices.

I can easily identify which pairs have duplicates by reversing the order of the x,y pairs and comparing to the original. But once identified, does someone have a method for removing and leaving unique values?

Thanks

 

[MickG]

eejwo :-
Would you be happy to solved this problem with some code ????

 

[eejwo]

Hi,

Yes, sure. I'd originally put "formula" in the thread title cause I thought it was reasonably straight forward problem, but I was just missing a trick. If you can solve it efficiently with VBA I'm all ears...

 

[shg]

I'd use a simple formula to swap the coordinates if necessary, and then use Data > Remove Duplicates.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][td="bgcolor:#C0C0C0"]

J​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

x1​

[/td][td="bgcolor:#F3F3F3"]

y1​

[/td][td="bgcolor:#F3F3F3"]

x2​

[/td][td="bgcolor:#F3F3F3"]

y2​

[/td][td="bgcolor:#F3F3F3"]

x1​

[/td][td="bgcolor:#F3F3F3"]

y1​

[/td][td="bgcolor:#F3F3F3"]

x2​

[/td][td="bgcolor:#F3F3F3"]

y2​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td="bgcolor:#CCFFCC"]

3​

[/td][td="bgcolor:#CCFFCC"]

4​

[/td][td][/td][td="bgcolor:#CCFFCC"]E2:H2: {=IF($A2<=$C2, A2:D2, CHOOSE({1,2,3,4}, C2, D2, A2, B2))}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

3​

[/td][td]

4​

[/td][td]

1​

[/td][td]

2​

[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td="bgcolor:#CCFFCC"]

3​

[/td][td="bgcolor:#CCFFCC"]

4​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

5​

[/td][td]

6​

[/td][td]

7​

[/td][td]

8​

[/td][td="bgcolor:#CCFFCC"]

5​

[/td][td="bgcolor:#CCFFCC"]

6​

[/td][td="bgcolor:#CCFFCC"]

7​

[/td][td="bgcolor:#CCFFCC"]

8​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

5​

[/td][td]

6​

[/td][td]

7​

[/td][td]

8​

[/td][td="bgcolor:#CCFFCC"]

5​

[/td][td="bgcolor:#CCFFCC"]

6​

[/td][td="bgcolor:#CCFFCC"]

7​

[/td][td="bgcolor:#CCFFCC"]

8​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

1​

[/td][td]

3​

[/td][td]

5​

[/td][td]

7​

[/td][td="bgcolor:#CCFFCC"]

1​

[/td][td="bgcolor:#CCFFCC"]

3​

[/td][td="bgcolor:#CCFFCC"]

5​

[/td][td="bgcolor:#CCFFCC"]

7​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

2​

[/td][td]

4​

[/td][td]

6​

[/td][td]

8​

[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td="bgcolor:#CCFFCC"]

4​

[/td][td="bgcolor:#CCFFCC"]

6​

[/td][td="bgcolor:#CCFFCC"]

8​

[/td][td][/td][td][/td][/tr]
[/table]

 

[eejwo]

I'd use a simple formula to swap the coordinates if necessary, and then use Data > Remove Duplicates.

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[TD="bgcolor: #C0C0C0"]

D​

[/TD]
[TD="bgcolor: #C0C0C0"]

E​

[/TD]
[TD="bgcolor: #C0C0C0"]

F​

[/TD]
[TD="bgcolor: #C0C0C0"]

G​

[/TD]
[TD="bgcolor: #C0C0C0"]

H​

[/TD]
[TD="bgcolor: #C0C0C0"]

I​

[/TD]
[TD="bgcolor: #C0C0C0"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

1​

[/TD]
[TD="bgcolor: #F3F3F3"]

x1​

[/TD]
[TD="bgcolor: #F3F3F3"]

y1​

[/TD]
[TD="bgcolor: #F3F3F3"]

x2​

[/TD]
[TD="bgcolor: #F3F3F3"]

y2​

[/TD]
[TD="bgcolor: #F3F3F3"]

x1​

[/TD]
[TD="bgcolor: #F3F3F3"]

y1​

[/TD]
[TD="bgcolor: #F3F3F3"]

x2​

[/TD]
[TD="bgcolor: #F3F3F3"]

y2​

[/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD="bgcolor: #CCFFCC"]

1​

[/TD]
[TD="bgcolor: #CCFFCC"]

2​

[/TD]
[TD="bgcolor: #CCFFCC"]

3​

[/TD]
[TD="bgcolor: #CCFFCC"]

4​

[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]E2:H2: {=IF($A2<=$C2, A2:D2, CHOOSE({1,2,3,4}, C2, D2, A2, B2))}[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD="bgcolor: #CCFFCC"]

1​

[/TD]
[TD="bgcolor: #CCFFCC"]

2​

[/TD]
[TD="bgcolor: #CCFFCC"]

3​

[/TD]
[TD="bgcolor: #CCFFCC"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD="bgcolor: #CCFFCC"]

5​

[/TD]
[TD="bgcolor: #CCFFCC"]

6​

[/TD]
[TD="bgcolor: #CCFFCC"]

7​

[/TD]
[TD="bgcolor: #CCFFCC"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD="bgcolor: #CCFFCC"]

5​

[/TD]
[TD="bgcolor: #CCFFCC"]

6​

[/TD]
[TD="bgcolor: #CCFFCC"]

7​

[/TD]
[TD="bgcolor: #CCFFCC"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

6​

[/TD]
[TD]

1​

[/TD]
[TD]

3​

[/TD]
[TD]

5​

[/TD]
[TD]

7​

[/TD]
[TD="bgcolor: #CCFFCC"]

1​

[/TD]
[TD="bgcolor: #CCFFCC"]

3​

[/TD]
[TD="bgcolor: #CCFFCC"]

5​

[/TD]
[TD="bgcolor: #CCFFCC"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

7​

[/TD]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[TD]

6​

[/TD]
[TD]

8​

[/TD]
[TD="bgcolor: #CCFFCC"]

2​

[/TD]
[TD="bgcolor: #CCFFCC"]

4​

[/TD]
[TD="bgcolor: #CCFFCC"]

6​

[/TD]
[TD="bgcolor: #CCFFCC"]

8​

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

Nice 'n' simple. That does the trick! Knew there'd be an easy solution, just needed to look at the problem from a different angle.

First time using the forum - a pleasant, efficient and educational experience. Thanks all for contributions, I'm sure I'll have another problem sooner rather than later.

 

[MickG]

Code Option :-

[COLOR="Navy"]Sub[/COLOR] MG29Nov59
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range, Al [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] t
t = Timer
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Txt = ""
    [COLOR="Navy"]Set[/COLOR] Al = CreateObject("System.Collections.ArrayList")
        [COLOR="Navy"]For[/COLOR] Ac = 0 To 3
            [COLOR="Navy"]If[/COLOR] Not Al.Contains(Dn.Offset(, Ac).Value) [COLOR="Navy"]Then[/COLOR] Al.Add Dn.Offset(, Ac).Value
        [COLOR="Navy"]Next[/COLOR] Ac
        Al.Sort
        Txt = Join(Al.toarray, ",")
        [COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
            .Add Txt, Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Dn
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete

[COLOR="Navy"]End[/COLOR] With
MsgBox Timer - t
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[AlanY]

will this pick up all the flipped pairs?

Book1
	A	B	C	D	E	F
1	x1	y1	x2	y2
2	1	2	3	4		Y
3	3	4	1	2		Y
4	5	6	7	8
5	5	6	7	8
6	1	3	5	7		Y
7	2	4	6	8
8	3	4	1	2		Y
9	5	7	1	3		Y
Sheet1

Cell Formulas
Range	Formula
F2	{=IFERROR(IF(MATCH(A2&"/"&B2,C$2:C$100&"/"&D$2:D$100,0)=MATCH(C2&"/"&D2,A$2:A$100&"/"&B$2:B$100,0),"Y",""),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.