calculating shortest distance between two points

[captainq]

I have a set of 9 nodes, with the distance between each pair of connected nodes. For example:

Node distance
(1,2) 72
(1,3) 31
(3,4) 81

I need a macro that will calculate the shortest distance between node 1 and each of the other nodes (2,3 and 4), and than will create a table that will sort the result by ascending distance.

The result I am expecting would be

(1,3) 31
(1,2) 72
(1,4) 112

I am new to macros. Anyone can help ?

[MrShorty]

Does it have to be a VBA macro? Here's an implementation of the shortest path problem that uses worksheet functions and the built in Solver: https://www.excel-easy.com/examples/...h-problem.html

[MarvinP]

Hi captain and welcome to the forum,

Are your "nodes" really cartesian points like (x,y) points on a plane? When you write node (1,2) is that point (1,2) or 2 different nodes where we don't know where they are?

I think I can solve your problem but need an example workbook with a few answers.

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[captainq]

They are 2 different nodes. Attached is a sample worksheet

[JeteMc]

If a formula based solution is acceptable then try the following as modeled on the 'Before' sheet:
1. Populate G2:G7 using: =IFERROR(AGGREGATE(15,6,C$2:C$6,ROWS(A$1:A1)),"")
2. Populate E2:F7 using: =IF($G2="","",INDEX(A$2:A$6,AGGREGATE(15,6,(ROW(A$2:A$6)-ROW(A$1))/($C$2:$C$6=$G2),COUNTIFS($G$2:$G2,$G2))))
Let us know if you have any questions.