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

The problem is that when I posted the code, it cut the lines where they
shouldn't have been cut. When I copied the code that I posted earlier,
several lines had been changed. I tried to shorten the lines of code so that
wouldn't happen again. Try the new code below though since I changed a few
things. Make sure you are pasting the numbers starting in cell A2 and that
the sheet name is "autosort". Let me know if you have any more problems.

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
Dim rangetosort1 As Range
Dim rangetosort2 As Range

'num points
Sheets("AutoSort").Range("A2").Select 'select first point
Selection.End(xlDown).Select 'get last point

'if not enough points or to many points exist
If ActiveCell = Range("A65536") Then Range("A1").Select: _
MsgBox ("Either to many points or not enough points exist.") _
: End
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)

'taking care of xdelta and ydelta 0's
If xdelta = 0 Then xdelta = 0.000000001
If ydelta = 0 Then ydelta = 0.000000001

'depending on quadrant, may have to add degrees

degadd = 0 'clear degadd

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

Next i

Set rangetosort1 = Sheets("autosort").Cells(1, 1)
Set rangetosort2 = Sheets("autosort").Cells(numpoints + 1, 5)

Sheets("autosort").Range(rangetosort1, rangetosort2).Select
Range(rangetosort1, rangetosort2).Sort Key1:=Range("E2"), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

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

-Chris



"phattony72" wrote:


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

Thank you soooooo much. I love it. The only problem I had was with the
following code. It gave me the error message "Compile Error: Variable
not defined" and Highlighted the x|SortNormal. If I delete or comment
the code below it works great, it's just not sorted. I could easily do
that with a click of the sort button, but if it worked in the code that
would be awesome.

Also, you wrote somewhere about being able to make a button. Is that
true? Could you make a button on the tool bar to make it awesomely
user friendly? That way we could just paste any data into this one
workbook and it would sort it all out.

Sorry for so many questions. You've been an amazing help.


CWillis Wrote:

Set rangetosort1 = Sheets("autosort").Cells(1, 1)
Set rangetosort2 = Sheets("autosort").Cells(numpoints + 1, 5)

Sheets("autosort").Range(rangetosort1, rangetosort2).Select
Range(rangetosort1, rangetosort2).Sort Key1:=Range("E2"), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


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

Make sure that the following is at the top of your code with the rest of the
"dim" statements:

Dim rangetosort1 As Range
Dim rangetosort2 As Range

If that doesn't fix the error message, let me know.

As for the button, it is a great way to execute code without having to go
back and forth between the screens. The easiest way for me to make a button
is:

1. Open up the "Control Toolbox" toolbar. (On the main excel page, Right
click on the top toolbars and select "Control Toolbox.")

2. On that toolbar, there is a square box. When you put the cursor over it,
it will read "Command Button". Click on that.

3. Now to draw the button, click on the cells and drag the button to the
shape you want it. (You can resize it anytime.)

4. Notice that when you put your cursor over your new button, the cursor
turns into four arrows. This is becuase you are in "Design Mode." To toggle
design mode on and off, look on your new "Control Toolbox" toolbar for an
icon with a pencil, ruler and a triangle.

5. While your new button is selected, look for the hand holding the piece of
paper also on that toolbar. This is the properties button. Here you can
change the properties of the button including color, captions, names, and
much more. Mess around with it, just save first!

6. Another button on your new toolbar is a piece of paper with a magnifying
glass. Push it. You will be taken to the VBA screen and you will be inside
the code that is executed when this button is pushed. The easiest way to
assign your new button with the code we have been working on is to copy the
code we have made and paste it underneath where it says "Private Sub
Yourbutton'sname_Click()" but before "end sub". Make sure you take out the
two lines of code from before that say "Sub ....." and "End Sub" You only
need to define the sub once and end the sub once.

7. Now, go back to the workbook. Click on the design mode button to turn it
off. Now pressing the button should execute the code and your numbers should
automatically sort.

Well, I hope I haven't confused you to much. Let me know if you have any
problems with that.

-Chris

"phattony72" wrote:


Thank you soooooo much. I love it. The only problem I had was with the
following code. It gave me the error message "Compile Error: Variable
not defined" and Highlighted the x|SortNormal. If I delete or comment
the code below it works great, it's just not sorted. I could easily do
that with a click of the sort button, but if it worked in the code that
would be awesome.

Also, you wrote somewhere about being able to make a button. Is that
true? Could you make a button on the tool bar to make it awesomely
user friendly? That way we could just paste any data into this one
workbook and it would sort it all out.

Sorry for so many questions. You've been an amazing help.


CWillis Wrote:

Set rangetosort1 = Sheets("autosort").Cells(1, 1)
Set rangetosort2 = Sheets("autosort").Cells(numpoints + 1, 5)

Sheets("autosort").Range(rangetosort1, rangetosort2).Select
Range(rangetosort1, rangetosort2).Sort Key1:=Range("E2"), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


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

CWillis,

That's great. I got the button put on the toolbar. It runs the
macro.

I'm still having the problem with the sorth though. I do have those
lines at the beginning of the code. The part that runs the sorting
part kind of looks like maybe there are line breaks in weird parts,
like you talked about in your earlier post, but I'm not sure where
those might be. I really don't know much about writing and
troubleshooting code.

Also, is there a way that I can make it so that people can't change or
accidentally save this file? I'd like them to be able to make changes
and then do a save as, but only save as and not just regular save. Is
this possible?

Thanks so much.


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

Phattony,

I tried the code on a different computer. It sorts for me, so I'm not sure
what to tell you. The way I write a lot of my code is by recording it and
then altering it. I will have you try that and see if it fixes it. Go to
the portion of the code that sorts the data (Starting with the line "Set
rangetosort1 = Sheets("autosort").Cells(1, 1)" and ending with the line "
DataOption1:=xlSortNormal".) Highlight all of the code and comment it out.
To do this you will need the "Edit Toolbar" active in the VBA screen. The
comment button is the button with black and blue lines. The one next to it
with the arrow is the "Uncomment" button. Now that the code is commented out
(Should be green) it won't do anything.

Now, go back to the Autosort page. You will need sample data. Go to
ToolsMacroRecord New Macro. Hit "OK" when the pop-up appears. Now
anything you do in excel will be recorded in a macro until you stop
recording. Highlight all of the data and then go to DataSort and sort like
you have been.

Now go to ToolMacroStop Recording. Now go back to the VBA screen. On the
left hand side of the screen there should be a folder named "Microsof Excel
Objects" and another folder below it named "Modules". Double click on
Modules and then Double click on Module1. There is the code you just
recorded. The problem is that that code only will work for the ranges you
sorted last time. That won't work for us since you said you have different
numbers of points every time. There may be a way to do it by columns, but
that isn't how I approached it.

So, copy everything in black. (Don't copy the "Sub Macro1() or the End Sub
but copy everything else in black.) Now, on the left double click "Sheet1
(AutoSort)". Paste the new code underneath the code we commented earlier.
(Hopefully it will look different, otherwise I don't know what the problem
is.)

Now, paste the following code above what you just pasted:

Set rangetosort1 = Sheets("autosort").Cells(1, 1)
Set rangetosort2 = Sheets("autosort").Cells(numpoints + 1, 5)

That sets the selected range to how many points you have. (See the .Cells
portion. That is one way VBA calls cells. .Cells(rows,columns). A range
must have to cells. Rangetosort1 is the first cell of the sheet. Cell(1,1).
Rangetosort2 is the other bound of the range we want to sort. The column is
always column 5 but the row number changes depending on how many points there
are. That is why rangetosort2 is .cells(numpoints+1). The numpoints is a
variable defined earlier. I added one to take into account the header name.)


Ok, now that we have assigned the variables that we want to sort, we have to
change your recorded code to sort these cells, not the cells we recorded.
The first line of the code you recorded,copied then pasted should say
"Range("A1:E7").Select" (A1:E7 will probably be different for you. That is
fine.) Delete the everything inside of the parenthesis. Make it look like
this:

Range(rangetosort1, rangetosort2).select

Let me know if that doesn't work. We will try and get someone else to help.

As far as the save as, I have done the same thing at my work. I did what
you want by using the document managment system at our company. I made the
file read only so they had to save it to their own location before editing
it. Excel does have password protection options, but that only helps with
editing, not saving as far as I know. You may want to post that as a new
question as well.

Let me know if you have more problems.

-Chris

"phattony72" wrote:


CWillis,

That's great. I got the button put on the toolbar. It runs the
macro.

I'm still having the problem with the sorth though. I do have those
lines at the beginning of the code. The part that runs the sorting
part kind of looks like maybe there are line breaks in weird parts,
like you talked about in your earlier post, but I'm not sure where
those might be. I really don't know much about writing and
troubleshooting code.

Also, is there a way that I can make it so that people can't change or
accidentally save this file? I'd like them to be able to make changes
and then do a save as, but only save as and not just regular save. Is
this possible?

Thanks so much.


--
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

I have Vet's Day Parade entries that come in over time. Sometimes I wonder
how I got hooked to do this. When you retire they ask for more!!! Any way
Excel only sorts 111222333 not, 123123123 so that is my problem useing column
(D) nbrs will be scrambled Need to sort into groups up tp 10 or so. If Parade
gets bigger will have to adjust later.What I am looking for is first column.
I am stumped.
Not
1 1
2 1
3 2
4 2
1 3
2 3
3 4
4 4
Hope this tells story Let me know if it doesn't
Thanks

"CWillis" wrote:

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

Curt,

I don't know if this is what you are looking for but with my data starting
in A5 I entered in a Helper column in the same row the formula:
=COUNTIF($A$5:A5,A5)
and copied down. I then highlighted all the data including the Helper
column and sorted on the Helper column. Next I highlighted all the 1's and
sorted, (with no header), on columnn A, highlighted all the 2's etc.

You could write code to automate it but unless you have a great deal of data
then it would hardly be worth it.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Curt" wrote in message
...
I have Vet's Day Parade entries that come in over time. Sometimes I wonder
how I got hooked to do this. When you retire they ask for more!!! Any way
Excel only sorts 111222333 not, 123123123 so that is my problem useing
column
(D) nbrs will be scrambled Need to sort into groups up tp 10 or so. If
Parade
gets bigger will have to adjust later.What I am looking for is first
column.
I am stumped.
Not
1 1
2 1
3 2
4 2
1 3
2 3
3 4
4 4
Hope this tells story Let me know if it doesn't
Thanks

"CWillis" wrote:

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

My results with countif has been that I get all 1's in a group then all 2's
in a group. Is not this what you got?
I needed a group that is 123456789 in order then another group and another.
Thanks

"Sandy Mann" wrote:

Curt,

I don't know if this is what you are looking for but with my data starting
in A5 I entered in a Helper column in the same row the formula:
=COUNTIF($A$5:A5,A5)
and copied down. I then highlighted all the data including the Helper
column and sorted on the Helper column. Next I highlighted all the 1's and
sorted, (with no header), on columnn A, highlighted all the 2's etc.

You could write code to automate it but unless you have a great deal of data
then it would hardly be worth it.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Curt" wrote in message
...
I have Vet's Day Parade entries that come in over time. Sometimes I wonder
how I got hooked to do this. When you retire they ask for more!!! Any way
Excel only sorts 111222333 not, 123123123 so that is my problem useing
column
(D) nbrs will be scrambled Need to sort into groups up tp 10 or so. If
Parade
gets bigger will have to adjust later.What I am looking for is first
column.
I am stumped.
Not
1 1
2 1
3 2
4 2
1 3
2 3
3 4
4 4
Hope this tells story Let me know if it doesn't
Thanks

"CWillis" wrote:

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

"Curt" wrote in message
...
My results with countif has been that I get all 1's in a group then all
2's

If you mean you got that after you sorted on the Helper column then yes that
is what I got. I then went on to sort the 1's only, sorting on column A.

If you mean that you got the numbers grouped together in the Helper column
without having to sort on the Helper column then you don't have to sort on
the Helper column. In either case when you get your data like:

A_____B______C
4__Other Data__1
3__Other Data__1
1__Other Data__1
2__Other Data__1
3__Other Data__2
1__Other Data__2
4__Other Data__2
2__Other Data__2
3__Other Data__3
4__Other Data__3
1__Other Data__3
2__Other Data__3

Highlight all data in all columns in the rows with the 1's grouped in the
Helper column, (C in the above example), and sort, (without headers), on
column A, then highlight all 2's in the Helper column etc.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Curt" wrote in message
...
My results with countif has been that I get all 1's in a group then all
2's
in a group. Is not this what you got?
I needed a group that is 123456789 in order then another group and
another.
Thanks

"Sandy Mann" wrote:

Curt,

I don't know if this is what you are looking for but with my data
starting
in A5 I entered in a Helper column in the same row the formula:
=COUNTIF($A$5:A5,A5)
and copied down. I then highlighted all the data including the Helper
column and sorted on the Helper column. Next I highlighted all the 1's
and
sorted, (with no header), on columnn A, highlighted all the 2's etc.

You could write code to automate it but unless you have a great deal of
data
then it would hardly be worth it.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Curt" wrote in message
...
I have Vet's Day Parade entries that come in over time. Sometimes I
wonder
how I got hooked to do this. When you retire they ask for more!!! Any
way
Excel only sorts 111222333 not, 123123123 so that is my problem useing
column
(D) nbrs will be scrambled Need to sort into groups up tp 10 or so. If
Parade
gets bigger will have to adjust later.What I am looking for is first
column.
I am stumped.
Not
1 1
2 1
3 2
4 2
1 3
2 3
3 4
4 4
Hope this tells story Let me know if it doesn't
Thanks

"CWillis" wrote:

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

Thanks for your post
Have gotten code to sort in order finally. Took a person better than me to
do it. Just learning at my age is fun.
Thanks again
Curt

"Sandy Mann" wrote:

"Curt" wrote in message
...
My results with countif has been that I get all 1's in a group then all
2's

If you mean you got that after you sorted on the Helper column then yes that
is what I got. I then went on to sort the 1's only, sorting on column A.

If you mean that you got the numbers grouped together in the Helper column
without having to sort on the Helper column then you don't have to sort on
the Helper column. In either case when you get your data like:

A_____B______C
4__Other Data__1
3__Other Data__1
1__Other Data__1
2__Other Data__1
3__Other Data__2
1__Other Data__2
4__Other Data__2
2__Other Data__2
3__Other Data__3
4__Other Data__3
1__Other Data__3
2__Other Data__3

Highlight all data in all columns in the rows with the 1's grouped in the
Helper column, (C in the above example), and sort, (without headers), on
column A, then highlight all 2's in the Helper column etc.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Curt" wrote in message
...
My results with countif has been that I get all 1's in a group then all
2's
in a group. Is not this what you got?
I needed a group that is 123456789 in order then another group and
another.
Thanks

"Sandy Mann" wrote:

Curt,

I don't know if this is what you are looking for but with my data
starting
in A5 I entered in a Helper column in the same row the formula:
=COUNTIF($A$5:A5,A5)
and copied down. I then highlighted all the data including the Helper
column and sorted on the Helper column. Next I highlighted all the 1's
and
sorted, (with no header), on columnn A, highlighted all the 2's etc.

You could write code to automate it but unless you have a great deal of
data
then it would hardly be worth it.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Curt" wrote in message
...
I have Vet's Day Parade entries that come in over time. Sometimes I
wonder
how I got hooked to do this. When you retire they ask for more!!! Any
way
Excel only sorts 111222333 not, 123123123 so that is my problem useing
column
(D) nbrs will be scrambled Need to sort into groups up tp 10 or so. If
Parade
gets bigger will have to adjust later.What I am looking for is first
column.
I am stumped.
Not
1 1
2 1
3 2
4 2
1 3
2 3
3 4
4 4
Hope this tells story Let me know if it doesn't
Thanks

"CWillis" wrote:

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

You're Very welcome Curt.

do it. Just learning at my age is fun.

Learning at any age is fun - that's why I'm here.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Curt" wrote in message
...
Thanks for your post
Have gotten code to sort in order finally. Took a person better than me to
do it. Just learning at my age is fun.
Thanks again
Curt

"Sandy Mann" wrote:

"Curt" wrote in message
...
My results with countif has been that I get all 1's in a group then all
2's

If you mean you got that after you sorted on the Helper column then yes
that
is what I got. I then went on to sort the 1's only, sorting on column A.

If you mean that you got the numbers grouped together in the Helper
column
without having to sort on the Helper column then you don't have to sort
on
the Helper column. In either case when you get your data like:

A_____B______C
4__Other Data__1
3__Other Data__1
1__Other Data__1
2__Other Data__1
3__Other Data__2
1__Other Data__2
4__Other Data__2
2__Other Data__2
3__Other Data__3
4__Other Data__3
1__Other Data__3
2__Other Data__3

Highlight all data in all columns in the rows with the 1's grouped in the
Helper column, (C in the above example), and sort, (without headers), on
column A, then highlight all 2's in the Helper column etc.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Curt" wrote in message
...
My results with countif has been that I get all 1's in a group then all
2's
in a group. Is not this what you got?
I needed a group that is 123456789 in order then another group and
another.
Thanks

"Sandy Mann" wrote:

Curt,

I don't know if this is what you are looking for but with my data
starting
in A5 I entered in a Helper column in the same row the formula:
=COUNTIF($A$5:A5,A5)
and copied down. I then highlighted all the data including the Helper
column and sorted on the Helper column. Next I highlighted all the
1's
and
sorted, (with no header), on columnn A, highlighted all the 2's etc.

You could write code to automate it but unless you have a great deal
of
data
then it would hardly be worth it.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Curt" wrote in message
...
I have Vet's Day Parade entries that come in over time. Sometimes I
wonder
how I got hooked to do this. When you retire they ask for more!!!
Any
way
Excel only sorts 111222333 not, 123123123 so that is my problem
useing
column
(D) nbrs will be scrambled Need to sort into groups up tp 10 or so.
If
Parade
gets bigger will have to adjust later.What I am looking for is first
column.
I am stumped.
Not
1 1
2 1
3 2
4 2
1 3
2 3
3 4
4 4
Hope this tells story Let me know if it doesn't
Thanks

"CWillis" wrote:

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