compare all distances and match with the closest item

kamiltuncer

New Member
Joined
Jul 26, 2011
Messages
2
Hello everybody

Consider I have something like attached.

What I need to do is; compare distances between A1-B1, A1-B2, A1-B3... etc. and match A1 with the closest one (in this case it is B1)

I have X-Y coordinates for all items here.

I attached a screenshot of a sample excel file to make it more explanatory.

I did this with calculating differences between X & Y coordinates and apply pythagoras theorem but the problem is I have to write very long formulas in each cell.

I believe very small VBA code can solve this but Im zero with VBA.

Can anybody help about this issue please

Dropbox - Untitled.png

Dropbox - Untitled2.png

Thanks
 
Last edited:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
try this with a slim down version of your data

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">X</td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">X</td><td style=";">Y</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">A1</td><td style="text-align: right;;">37</td><td style="text-align: right;;">8</td><td style="background-color: #E2EFDA;;">B7</td><td style="text-align: right;;"></td><td style=";">B1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">45</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">A2</td><td style="text-align: right;;">14</td><td style="text-align: right;;">40</td><td style="background-color: #E2EFDA;;">B4</td><td style="text-align: right;;"></td><td style=";">B2</td><td style="text-align: right;;">6</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">A3</td><td style="text-align: right;;">40</td><td style="text-align: right;;">34</td><td style="background-color: #E2EFDA;;">B5</td><td style="text-align: right;;"></td><td style=";">B3</td><td style="text-align: right;;">14</td><td style="text-align: right;;">26</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">A4</td><td style="text-align: right;;">47</td><td style="text-align: right;;">10</td><td style="background-color: #E2EFDA;;">B7</td><td style="text-align: right;;"></td><td style=";">B4</td><td style="text-align: right;;">14</td><td style="text-align: right;;">42</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">A5</td><td style="text-align: right;;">36</td><td style="text-align: right;;">27</td><td style="background-color: #E2EFDA;;">B10</td><td style="text-align: right;;"></td><td style=";">B5</td><td style="text-align: right;;">43</td><td style="text-align: right;;">43</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">A6</td><td style="text-align: right;;">48</td><td style="text-align: right;;">49</td><td style="background-color: #E2EFDA;;">B8</td><td style="text-align: right;;"></td><td style=";">B6</td><td style="text-align: right;;">22</td><td style="text-align: right;;">48</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">A7</td><td style="text-align: right;;">14</td><td style="text-align: right;;">34</td><td style="background-color: #E2EFDA;;">B10</td><td style="text-align: right;;"></td><td style=";">B7</td><td style="text-align: right;;">23</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">A8</td><td style="text-align: right;;">44</td><td style="text-align: right;;">13</td><td style="background-color: #E2EFDA;;">B7</td><td style="text-align: right;;"></td><td style=";">B8</td><td style="text-align: right;;">47</td><td style="text-align: right;;">47</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">A9</td><td style="text-align: right;;">35</td><td style="text-align: right;;">37</td><td style="background-color: #E2EFDA;;">B5</td><td style="text-align: right;;"></td><td style=";">B9</td><td style="text-align: right;;">50</td><td style="text-align: right;;">44</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">A10</td><td style="text-align: right;;">25</td><td style="text-align: right;;">28</td><td style="background-color: #E2EFDA;;">B10</td><td style="text-align: right;;"></td><td style=";">B10</td><td style="text-align: right;;">19</td><td style="text-align: right;;">29</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">{=INDEX(<font color="Blue">$F$2:$F$11,MATCH(<font color="Red">MIN(<font color="Green">(<font color="Purple">(<font color="Teal">$B2-$G$2:$G$11</font>)^2+(<font color="Teal">$C2-$H$2:$H$11</font>)^2</font>)^0.5</font>),(<font color="Green">(<font color="Purple">$B2-$G$2:$G$11</font>)^2+(<font color="Purple">$C2-$H$2:$H$11</font>)^2</font>)^0.5,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

kamiltuncer

New Member
Joined
Jul 26, 2011
Messages
2
Well Alan Thank you very much. I must immediately start understanding the logic of INDEX formula

cheers
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,099,462
Messages
5,468,786
Members
406,608
Latest member
G3TEN

This Week's Hot Topics

Top