Hi there,

I've got a list of data in two columns that are X-Y coordinates for
points in an image. I save this to a .txt file, then open it in some
other software I have. This all works fine. The problem is the data,
when plotted, form a circle and the line that connects the point goes
in order of the list. Which means if the list is out of order I get
the points in the right places but the line connecting them goes all
over the place.

What I need is a way to easily sort the data into a circle. Confusing
huh?

This doesn't have to be done in excel if anyone knows how I could do it
in another program.


--
phattony72
------------------------------------------------------------------------
phattony72's Profile: http://www.excelforum.com/member.php...o&userid=37662
View this thread: http://www.excelforum.com/showthread...hreadid=572700

If my understanding of you problem is correct, I don't think it is
possible to "sort" this data set...

To make a circle, your data set will have to loop around. IE:

0,0
0,1
1,1
1,0
0,0

(I know this would form a square and not a circle, but simplified for
explanation)

You can't "SORT" this set of data ascending or descending…


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=572700

I'm interested. If possible, could you please provide sample data and I will
give it a try.

"phattony72" wrote:


Hi there,

I've got a list of data in two columns that are X-Y coordinates for
points in an image. I save this to a .txt file, then open it in some
other software I have. This all works fine. The problem is the data,
when plotted, form a circle and the line that connects the point goes
in order of the list. Which means if the list is out of order I get
the points in the right places but the line connecting them goes all
over the place.

What I need is a way to easily sort the data into a circle. Confusing
huh?

This doesn't have to be done in excel if anyone knows how I could do it
in another program.


--
phattony72
------------------------------------------------------------------------
phattony72's Profile: http://www.excelforum.com/member.php...o&userid=37662
View this thread: http://www.excelforum.com/showthread...hreadid=572700

Yeah, I know that a simple sort won't do it. I'm trying to think of
anything else that can be done.

I've included my sample set as a txt file (easy to open with excel,
just comma delimited). It's ok with this one because there are only a
few points, but I will have others with thousands of points. You'll
notice that the two LINE sets form vague circles (if you do a simple
x-y scatter you can see this).

Thanks.


+-------------------------------------------------------------------+
|Filename: T1_2_outlines.txt |
|Download: http://www.excelforum.com/attachment.php?postid=5221 |
+-------------------------------------------------------------------+

--
phattony72
------------------------------------------------------------------------
phattony72's Profile: http://www.excelforum.com/member.php...o&userid=37662
View this thread: http://www.excelforum.com/showthread...hreadid=572700

Well, I ran out of time so it isn't finished, but it seems to work with the
data you gave me.

Quick explanation:
Finds the center of the circle (this is the biggest problem. It currently
finds the center by averaging all x's and all y's. This is fine as long as
the points are nicely distributed around the circle. This will cause a
problem if most points are on one side for example.) After it finds the
center, it finds the angle of the imaginary line between the center and the
actual point. Using the angle, you can sort the data points so they come out
nice and neat when you plot them.

Here is what you need to do:
On a worksheet named "AutoSort", in row 1 starting with cell one, but the
following titles:
X Value
Y Value
xdelta
ydelta
theta (degrees)

alt+f11 takes you into the vba screen. Paste the following code:
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''
Option Explicit
sub testing()
'Private Sub CommandButton1_Click()
Dim numpoints As Integer
Dim xsum
Dim ysum
Dim xaverage
Dim yaverage
Dim xdelta
Dim ydelta
Dim degadd As Integer
Dim i As Integer
Dim pi

'num points
Sheets("AutoSort").Range("A2").Select 'select first point
Selection.End(xlDown).Select 'get last point
numpoints = ActiveCell.Row - 1 'gets num points

'averages (current way of finding center of circle)
For i = 1 To numpoints
xsum = xsum + Sheets("autosort").Cells(i + 1, 1).Value
ysum = ysum + Sheets("autosort").Cells(i + 1, 2).Value
Next i

xaverage = xsum / numpoints
yaverage = ysum / numpoints

'xdelta and ydelta
For i = 1 To numpoints
Sheets("autosort").Cells(i + 1, 3).Value =
Sheets("autosort").Cells(i + 1, 1).Value - xaverage
Sheets("autosort").Cells(i + 1, 4).Value =
Sheets("autosort").Cells(i + 1, 2).Value - yaverage
Next i

'theta in degrees

pi = 4 * Atn(1) ' Calculate the value of pi.
For i = 1 To numpoints
xdelta = Sheets("autosort").Cells(i + 1, 3)
ydelta = Sheets("autosort").Cells(i + 1, 4)

'depending on quadrant, may have to add degrees
If xdelta 0 Then
If ydelta < 0 Then degadd = 360
End If

If xdelta < 0 Then degadd = 180

'display degree amount
Sheets("autosort").Cells(i + 1, 5).Value = degadd + Atn(ydelta /
xdelta) * 180 / pi
'Sheets("autosort").Cells(i + 1, 5).Value = Atn(ydelta / xdelta) *
180 / pi

Next i

End Sub
''''''''''''''''''''''''''''''''''''''''''''''

(You can make a button to run this if you so choose and then just uncomment
the second line.) Now, paste your data points under x value and y value and
run the code. It will display numbers under your column "theta (degrees)".

Now, go to data, sort and sort by "theta (degrees)". Your plot should work
when you plot the x and y values now.

Post any questions. I will be able to help on Monday.

Hope that helps.

-Chris

"phattony72" wrote:


Yeah, I know that a simple sort won't do it. I'm trying to think of
anything else that can be done.

I've included my sample set as a txt file (easy to open with excel,
just comma delimited). It's ok with this one because there are only a
few points, but I will have others with thousands of points. You'll
notice that the two LINE sets form vague circles (if you do a simple
x-y scatter you can see this).

Thanks.


+-------------------------------------------------------------------+
|Filename: T1_2_outlines.txt |
|Download: http://www.excelforum.com/attachment.php?postid=5221 |
+-------------------------------------------------------------------+

--
phattony72
------------------------------------------------------------------------
phattony72's Profile: http://www.excelforum.com/member.php...o&userid=37662
View this thread: http://www.excelforum.com/showthread...hreadid=572700

Great. My cousin and I actually came up with this idea (using theta)
last night. But I didn't know how to make it happen.

However, I'm having some trouble with the code. I opened vba, pasted
it in. Then I wasn't sure what to do. I went to macros and hit run,
then it said there was an error with the Sub line, line 1.

Thanks.


--
phattony72
------------------------------------------------------------------------
phattony72's Profile: http://www.excelforum.com/member.php...o&userid=37662
View this thread: http://www.excelforum.com/showthread...hreadid=572700

Ok, I think that last thing I posted was wrong. The error message is
"Syntax Error" and now I think it might be in the line 31. When it
goes to the VBA editor after the message, that line is selected. I
thought it was the first line because line 1 is highlighted. If this
isn't clear I'll try to explain it better.

Thanks for your help.


--
phattony72
------------------------------------------------------------------------
phattony72's Profile: http://www.excelforum.com/member.php...o&userid=37662
View this thread: http://www.excelforum.com/showthread...hreadid=572700

trying to sort consectlive 12345678 have entries in column D scrambled as
come in. Need to sort final as 12345678 from complete sheet. Can this be done?
Thanks


"CWillis" wrote:

Well, I ran out of time so it isn't finished, but it seems to work with the
data you gave me.

Quick explanation:
Finds the center of the circle (this is the biggest problem. It currently
finds the center by averaging all x's and all y's. This is fine as long as
the points are nicely distributed around the circle. This will cause a
problem if most points are on one side for example.) After it finds the
center, it finds the angle of the imaginary line between the center and the
actual point. Using the angle, you can sort the data points so they come out
nice and neat when you plot them.

Here is what you need to do:
On a worksheet named "AutoSort", in row 1 starting with cell one, but the
following titles:
X Value
Y Value
xdelta
ydelta
theta (degrees)

alt+f11 takes you into the vba screen. Paste the following code:
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''
Option Explicit
sub testing()
'Private Sub CommandButton1_Click()
Dim numpoints As Integer
Dim xsum
Dim ysum
Dim xaverage
Dim yaverage
Dim xdelta
Dim ydelta
Dim degadd As Integer
Dim i As Integer
Dim pi

'num points
Sheets("AutoSort").Range("A2").Select 'select first point
Selection.End(xlDown).Select 'get last point
numpoints = ActiveCell.Row - 1 'gets num points

'averages (current way of finding center of circle)
For i = 1 To numpoints
xsum = xsum + Sheets("autosort").Cells(i + 1, 1).Value
ysum = ysum + Sheets("autosort").Cells(i + 1, 2).Value
Next i

xaverage = xsum / numpoints
yaverage = ysum / numpoints

'xdelta and ydelta
For i = 1 To numpoints
Sheets("autosort").Cells(i + 1, 3).Value =
Sheets("autosort").Cells(i + 1, 1).Value - xaverage
Sheets("autosort").Cells(i + 1, 4).Value =
Sheets("autosort").Cells(i + 1, 2).Value - yaverage
Next i

'theta in degrees

pi = 4 * Atn(1) ' Calculate the value of pi.
For i = 1 To numpoints
xdelta = Sheets("autosort").Cells(i + 1, 3)
ydelta = Sheets("autosort").Cells(i + 1, 4)

'depending on quadrant, may have to add degrees
If xdelta 0 Then
If ydelta < 0 Then degadd = 360
End If

If xdelta < 0 Then degadd = 180

'display degree amount
Sheets("autosort").Cells(i + 1, 5).Value = degadd + Atn(ydelta /
xdelta) * 180 / pi
'Sheets("autosort").Cells(i + 1, 5).Value = Atn(ydelta / xdelta) *
180 / pi

Next i

End Sub
''''''''''''''''''''''''''''''''''''''''''''''

(You can make a button to run this if you so choose and then just uncomment
the second line.) Now, paste your data points under x value and y value and
run the code. It will display numbers under your column "theta (degrees)".

Now, go to data, sort and sort by "theta (degrees)". Your plot should work
when you plot the x and y values now.

Post any questions. I will be able to help on Monday.

Hope that helps.

-Chris

"phattony72" wrote:


Yeah, I know that a simple sort won't do it. I'm trying to think of
anything else that can be done.

I've included my sample set as a txt file (easy to open with excel,
just comma delimited). It's ok with this one because there are only a
few points, but I will have others with thousands of points. You'll
notice that the two LINE sets form vague circles (if you do a simple
x-y scatter you can see this).

Thanks.


+-------------------------------------------------------------------+
|Filename: T1_2_outlines.txt |
|Download: http://www.excelforum.com/attachment.php?postid=5221 |
+-------------------------------------------------------------------+

--
phattony72
------------------------------------------------------------------------
phattony72's Profile: http://www.excelforum.com/member.php...o&userid=37662
View this thread: http://www.excelforum.com/showthread...hreadid=572700

Sorry about the late reply. Just got back from vacation. Could you give me
a better description of the problem? Data-Sort-Descending should sort 123...
I'm assuming your problem is more complex. Sample data and a goal would be
best.

-Chris

"Curt" wrote:

trying to sort consectlive 12345678 have entries in column D scrambled as
come in. Need to sort final as 12345678 from complete sheet. Can this be done?
Thanks


"CWillis" wrote:

Well, I ran out of time so it isn't finished, but it seems to work with the
data you gave me.

Quick explanation:
Finds the center of the circle (this is the biggest problem. It currently
finds the center by averaging all x's and all y's. This is fine as long as
the points are nicely distributed around the circle. This will cause a
problem if most points are on one side for example.) After it finds the
center, it finds the angle of the imaginary line between the center and the
actual point. Using the angle, you can sort the data points so they come out
nice and neat when you plot them.

Here is what you need to do:
On a worksheet named "AutoSort", in row 1 starting with cell one, but the
following titles:
X Value
Y Value
xdelta
ydelta
theta (degrees)

alt+f11 takes you into the vba screen. Paste the following code:
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''
Option Explicit
sub testing()
'Private Sub CommandButton1_Click()
Dim numpoints As Integer
Dim xsum
Dim ysum
Dim xaverage
Dim yaverage
Dim xdelta
Dim ydelta
Dim degadd As Integer
Dim i As Integer
Dim pi

'num points
Sheets("AutoSort").Range("A2").Select 'select first point
Selection.End(xlDown).Select 'get last point
numpoints = ActiveCell.Row - 1 'gets num points

'averages (current way of finding center of circle)
For i = 1 To numpoints
xsum = xsum + Sheets("autosort").Cells(i + 1, 1).Value
ysum = ysum + Sheets("autosort").Cells(i + 1, 2).Value
Next i

xaverage = xsum / numpoints
yaverage = ysum / numpoints

'xdelta and ydelta
For i = 1 To numpoints
Sheets("autosort").Cells(i + 1, 3).Value =
Sheets("autosort").Cells(i + 1, 1).Value - xaverage
Sheets("autosort").Cells(i + 1, 4).Value =
Sheets("autosort").Cells(i + 1, 2).Value - yaverage
Next i

'theta in degrees

pi = 4 * Atn(1) ' Calculate the value of pi.
For i = 1 To numpoints
xdelta = Sheets("autosort").Cells(i + 1, 3)
ydelta = Sheets("autosort").Cells(i + 1, 4)

'depending on quadrant, may have to add degrees
If xdelta 0 Then
If ydelta < 0 Then degadd = 360
End If

If xdelta < 0 Then degadd = 180

'display degree amount
Sheets("autosort").Cells(i + 1, 5).Value = degadd + Atn(ydelta /
xdelta) * 180 / pi
'Sheets("autosort").Cells(i + 1, 5).Value = Atn(ydelta / xdelta) *
180 / pi

Next i

End Sub
''''''''''''''''''''''''''''''''''''''''''''''

(You can make a button to run this if you so choose and then just uncomment
the second line.) Now, paste your data points under x value and y value and
run the code. It will display numbers under your column "theta (degrees)".

Now, go to data, sort and sort by "theta (degrees)". Your plot should work
when you plot the x and y values now.

Post any questions. I will be able to help on Monday.

Hope that helps.

-Chris

"phattony72" wrote:


Yeah, I know that a simple sort won't do it. I'm trying to think of
anything else that can be done.

I've included my sample set as a txt file (easy to open with excel,
just comma delimited). It's ok with this one because there are only a
few points, but I will have others with thousands of points. You'll
notice that the two LINE sets form vague circles (if you do a simple
x-y scatter you can see this).

Thanks.


+-------------------------------------------------------------------+
|Filename: T1_2_outlines.txt |
|Download: http://www.excelforum.com/attachment.php?postid=5221 |
+-------------------------------------------------------------------+

--
phattony72
------------------------------------------------------------------------
phattony72's Profile: http://www.excelforum.com/member.php...o&userid=37662
View this thread: http://www.excelforum.com/showthread...hreadid=572700