Help comparing column values

NotASuperGenius

Board Regular
Joined
Jun 17, 2002
Messages
58
I am working on a spreadsheet that includes the location of ~80k global cities as defined by an internal system (Gov), Bing and Google. I would like the audit the data to identify entries where the internal system may be incorrect or where there is significant inconsistency between sources. Since the values are rarely identical, I added columns for the straight line distances between points to analyze. I would like to add a column (manually added in the example below) that describes how the sources compare to each other

- Are they all within 1Km of each other = Alignment
- Are they all in conflict (greater than 1KM) = Conflict
- Is Gov inconsistent with Bing & Google = Gov Variance
- Is Bing inconsistent with Gov and Google = Bing Variance
- Is Google inconsistent with Gov and Bing = Google Variance

* Note, not trying to determine which value(s) is/are correct. I am looking for a general idea of how location definition varies.

Sample data below. As always, thanks for the assist - NASG


<b>Excel 2016 (Windows) 32 bit</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 /><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><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #6FA8DC;;">Location</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #FFB84D;;">Gov</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #FFB84D;;"></td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #FFB84D;;">Bing</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #FFB84D;;"></td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #FFB84D;;">Google</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #FFB84D;;"></td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;">Gov vs. Bing</td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;">Gov vs. Google</td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;">Bing vs. Google</td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;">Alignment</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #E7E6E6;;">Alignement Values</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;">Query</td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;">Lat</td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;">Long</td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;">Lat</td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;">Long</td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;">Lat</td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;">Long</td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;">(km)</td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;">(km)</td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;">(km)</td><td style="font-weight: bold;text-align: center;background-color: #E7E6E6;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;background-color: #E7E6E6;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">New York, NY, US</td><td style="text-align: right;;">40.75348889</td><td style="text-align: right;background-color: #CFE2F3;;">-73.98096111</td><td style="text-align: right;;">40.7820015</td><td style="text-align: right;background-color: #CFE2F3;;">-73.83170319</td><td style="text-align: right;;">40.7127837</td><td style="text-align: right;background-color: #CFE2F3;;">-74.0059413</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">8.06</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">3.1</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">10.3</td><td style="text-align: right;background-color: #F8F8F8;;">Conflict</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Aligned</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Corpus Christi, TX, US</td><td style="text-align: right;;">27.79628889</td><td style="text-align: right;background-color: #CFE2F3;;">-97.40355</td><td style="text-align: right;;">27.79640961</td><td style="text-align: right;background-color: #CFE2F3;;">-97.40355682</td><td style="text-align: right;;">27.8005828</td><td style="text-align: right;background-color: #CFE2F3;;">-97.396381</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.01</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.53</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.53</td><td style="text-align: right;background-color: #F8F8F8;;">Aligned</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Conflict</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">ATL, GA, US</td><td style="text-align: right;;">33.640772</td><td style="text-align: right;background-color: #CFE2F3;;">-84.447868</td><td style="text-align: right;;">33.74831009</td><td style="text-align: right;background-color: #CFE2F3;;">-84.39111328</td><td style="text-align: right;;">33.7489954</td><td style="text-align: right;background-color: #CFE2F3;;">-84.3879824</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">8.12</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">8.23</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.19</td><td style="text-align: right;background-color: #F8F8F8;;">Gov Variance</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Gov Variance</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Macon, GA, US</td><td style="text-align: right;;">32.83608056</td><td style="text-align: right;background-color: #CFE2F3;;">-83.66258056</td><td style="text-align: right;;">32.83967972</td><td style="text-align: right;background-color: #CFE2F3;;">-83.62757874</td><td style="text-align: right;;">32.8406946</td><td style="text-align: right;background-color: #CFE2F3;;">-83.6324022</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">2.05</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">1.78</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.29</td><td style="text-align: right;background-color: #F8F8F8;;">Gov Variance</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Bing Variance</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Puerto Vallarta, , MX</td><td style="text-align: right;;">20.61448889</td><td style="text-align: right;background-color: #CFE2F3;;">-105.2393111</td><td style="text-align: right;;">20.60713959</td><td style="text-align: right;background-color: #CFE2F3;;">-105.2243729</td><td style="text-align: right;;">20.653407</td><td style="text-align: right;background-color: #CFE2F3;;">-105.2253316</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">1.09</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">2.84</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">3.2</td><td style="text-align: right;background-color: #F8F8F8;;">Conflict</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Google Variance</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Plano, TX, US</td><td style="text-align: right;;">33.04641111</td><td style="text-align: right;background-color: #CFE2F3;;">-96.74486944</td><td style="text-align: right;;">33.0207901</td><td style="text-align: right;background-color: #CFE2F3;;">-96.69924927</td><td style="text-align: right;;">33.0198431</td><td style="text-align: right;background-color: #CFE2F3;;">-96.6988856</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">3.18</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">3.24</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.07</td><td style="text-align: right;background-color: #F8F8F8;;">Gov Variance</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Halifax, NS, CA</td><td style="text-align: right;;">44.6488</td><td style="text-align: right;background-color: #CFE2F3;;">-63.57536111</td><td style="text-align: right;;">44.64960098</td><td style="text-align: right;background-color: #CFE2F3;;">-63.59469986</td><td style="text-align: right;;">44.6488625</td><td style="text-align: right;background-color: #CFE2F3;;">-63.5753196</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.95</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.01</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.95</td><td style="text-align: right;background-color: #F8F8F8;;">Aligned</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">EWR, NJ, US</td><td style="text-align: right;;">40.69069</td><td style="text-align: right;background-color: #CFE2F3;;">-74.17741</td><td style="text-align: right;;">40.69287872</td><td style="text-align: right;background-color: #CFE2F3;;">-74.18544769</td><td style="text-align: right;;">40.6895314</td><td style="text-align: right;background-color: #CFE2F3;;">-74.1744624</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.45</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.17</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.62</td><td style="text-align: right;background-color: #F8F8F8;;">Aligned</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Milan, IT</td><td style="text-align: right;;">45.46421944</td><td style="text-align: right;background-color: #CFE2F3;;">9.189780556</td><td style="text-align: right;;">45.45199966</td><td style="text-align: right;background-color: #CFE2F3;;">9.192770004</td><td style="text-align: right;;">45.4654219</td><td style="text-align: right;background-color: #CFE2F3;;">9.1859243</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.86</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.21</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.99</td><td style="text-align: right;background-color: #F8F8F8;;">Aligned</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">New Orleans, LA, US</td><td style="text-align: right;;">29.95368889</td><td style="text-align: right;background-color: #CFE2F3;;">-90.07776111</td><td style="text-align: right;;">29.95369911</td><td style="text-align: right;background-color: #CFE2F3;;">-90.07775116</td><td style="text-align: right;;">29.9510658</td><td style="text-align: right;background-color: #CFE2F3;;">-90.0715323</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.42</td><td style="text-align: right;color: #FF9900;background-color: #F8F8F8;;">0.41</td><td style="text-align: right;background-color: #F8F8F8;;">Aligned</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></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 />
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

cruisen

New Member
Joined
Jul 5, 2016
Messages
11
Dear NASG,

for my approach to get this done, there are some steps needed:


1.) Geometric cases:

for a complete picture, i suggest to consider to add 3 more cases. Even if rare, there is the possibility, that on coordinate is more or less in the middle of the two others. I call them "centred".


2.) Lookup table


On an other sheet (#2) create a lookup table like this:

ByteAlignment
0Aligned
1GOV centred
2BING centred
3GOOGLE variance
4GOOGLE centred
5BING vary
6GOV variance
7Conflict

<colgroup><col><col></colgroup><tbody>
</tbody>

And define a name to the table like "LUT" (for look up table)



3.) Criteria maximum distance



to make things more flexible, please also find space on sheet #2 to define your maximum allowed distance, in your case you mentioned 1 for 1 KM, and also define the name for it: "MD", for max distance.




4.) assigning alignment text

- first clear column M in your table


- enter in L3: =IF(H3>MD,1,0)

which tests if the distance is higher or lower your maximum distance, and if it is lower, outputs a "0"


- auto fill column L, M and N, down to the last row of your table



- enter in O3: =L3*2^2+M3*2+N3

which will calulate a number from 0 to 7 (decimal) from these 3 "bits"


- auto fill column O down to the last row of your table


- enter in K3: =VLOOKUP(O3,LUT,2,FALSE)

which will lookup the text from the look up table


- auto fill column K down to the last row of your table



5.) Remarks

I hope I did understand your problem correctly.

for further statistical analysis, the values in column O are easier to work with.


Regrads, NvK
 

cruisen

New Member
Joined
Jul 5, 2016
Messages
11
Huzzah! That was awesome. Thank you!
You are very welcome. :)

btw: i am Interested in how you calculate the distance between 2 coordinates, since as far as I know one should use great circle approximation and that the distance are getting shorter when you are moving from equator to one of the poles ...
 

Watch MrExcel Video

Forum statistics

Threads
1,102,655
Messages
5,488,120
Members
407,625
Latest member
Alanacran

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top