ummm, ummm, HELP!!!

P

phattony72

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

Bearacade

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

CWillis

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

phattony72

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

CWillis

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
 
P

phattony72

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

phattony72

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

Thanks for your help
 
C

CWillis

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
 
P

phattony72

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

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

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

CWillis

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
 
P

phattony72

CWillis,

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

I'm still having the problem with the sorth though. I do have thos
lines at the beginning of the code. The part that runs the sortin
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 wher
those might be. I really don't know much about writing an
troubleshooting code.

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

Thanks so much
 
C

CWillis

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
Tools>Macro>Record 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 Data>Sort and sort like
you have been.

Now go to Tool>Macro>Stop 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
 
C

Curt

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
[email protected]
 
C

CWillis

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
 
C

Curt

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
 
S

Sandy Mann

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

[email protected]
[email protected] with @tiscali.co.uk
 
S

Sandy Mann

Curt said:
My results with countif has been that I get all 1's in a group then all
2's
Click to expand...

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

[email protected]
[email protected] with @tiscali.co.uk
 
C

Curt

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
 
Top