Drawing lines between unsequential data points
Drawing lines between unsequential data points
(OP)
I have a series of data points with XY co-ordinates listed in 2 columns. These data points are connected by lines to form a network, but unsequential, ie. data point 1 is connected to point 3, point 2 is connected to 5 etc. The network connections are listed in seperate columns so that the full data set might look like:
Points Network
Pt X Y Pt1 Pt2
1 0 1 1 3
2 1 3 2 5 .... etc.
If the data points alone were plotted then lines would show connecting point 1 to 2, 2 to 3 etc. However, I would like to plot this network with a series of lines between the points as defined in the Network connections columns.
How can I plot the points and connecting lines automatically so that each connection is shown on the XY graph as a seperate series of 2 data points? I've tried using a VBMacro and using a VB Userform to define my own graph but can find no way of defining a line without a picture added to the form, and no picture facility appears to exist in Excel Visual Basic.
Points Network
Pt X Y Pt1 Pt2
1 0 1 1 3
2 1 3 2 5 .... etc.
If the data points alone were plotted then lines would show connecting point 1 to 2, 2 to 3 etc. However, I would like to plot this network with a series of lines between the points as defined in the Network connections columns.
How can I plot the points and connecting lines automatically so that each connection is shown on the XY graph as a seperate series of 2 data points? I've tried using a VBMacro and using a VB Userform to define my own graph but can find no way of defining a line without a picture added to the form, and no picture facility appears to exist in Excel Visual Basic.
corus





RE: Drawing lines between unsequential data points
so
node_num_1 x1 y1
node_num_2 x2 y2
empty row
node_num_3 x3 y3
node_num_4 x4 y4
empty row
will produce a series of vectors on your x y plot when you select the second and third columns.
Cheers
Greg Locock
SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Drawing lines between unsequential data points
I have ordered the data point iwo make the right couple.
then build by hand the the graph you need.
You could make a VBMacro that make the graph in an automated way. I'm not able to do it.
Let me know if help!
Onda
RE: Drawing lines between unsequential data points
The lookup function in Onda's sheet worked, whereas vlookup gave #value in the cell, for some reason. Leaving blank rows to give a separate series for each line would have been an ideal solution, but also didn't seem to work. I thought this was because the complete row isn't blank, only the cells below each set of data, but transferring the data to a blank sheet didn't produce separate series of lines for each set of data either.
It'd be interesting to know if lines could be drawn on a blank userform in a VBmacro, if it's at all possible.
corus
RE: Drawing lines between unsequential data points
Drawing lines in a macro appears to be only possible using the autoshapes, drawn on the worksheet, and not on a separate form. Oh well.
Thanks again.
corus
RE: Drawing lines between unsequential data points
There is some information about drawing on the spreadsheet using autoshapes here:
ht
I'm pretty sure I've seen examples of people drawing on forms with shapes, but I've never tried it or looked at the details.
Finally, if you do have another go at Greg's approach I'd suggest using =Index() to retrieve the data rather than =Vlookup, because it's much quicker (not that it makes much difference these days, unless you have a huge amount of data).
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Drawing lines between unsequential data points
I'm also not sure why VLOOKUP didn't work as opposed to LOOKUP used in ONDA's sheet. Probably some subtle little error in the input I missed.
corus
RE: Drawing lines between unsequential data points
They all give the same result, but Index is much faster, because it goes straight to the specified cell, rather than searching through a column until it finds the right value.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/