Drawing lines between unsequential data points

[corus]

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.

corus

 

[GregLocock]

Amazingly easy using Vlookup, and the fact that XY graphs plot line sgements only if you put in a blank row


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

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Onda]

Corus,

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

 

[corus]

Many thanks to you both.

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

 

[corus]

Got it now. A blank row/cell simply separates the data so that the lines are disjointed for each pair of points, but as a single series.
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

 

[IDS]

Corus - I'm still not clear exactly what it is you are trying to do, and why Greg's approach won't work.

There is some information about drawing on the spreadsheet using autoshapes here:

http://newtonexcelbach.wordpress.co...n-excel-7-creating-drawings-from-coordinates/

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/

 

[corus]

Thanks IDS, an excellent link but as they show, drawing seems restricted to the worksheet using autoshapes/lines rather than on a separate form. I have seen excel sheets that call up a .exe file to use the data within the spreadsheet and then produce drawings/contours etc. on a separate form, but outside of Excel.

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

 

[IDS]

I have added a VLookup and an Index version of the Lookup table given in the data_point_connection file.

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/