Calculate new coordinate with distance

[D1XII]

In my mind this is simple - it is probably an excel nightmare:

My company has a geographical map that needs thousands of coordinates pinned so it can be accessed through a map app. The coordinates are close - so I want to pin one coordinate and calculate the next by adding a few feet in between.

For example: 23° 9'28.19"N, 81°55'34.06"W becomes 23° 9'28.19"N, 81°55'34.48"W after adding ~10ft to the West.

In my mind the xls would be simple:

Reference Latitude	Reference Longitude	Distance (in feet)	Direction from Reference to New Point	New Lat	New Long
23° 9'28.19"N	81°55'34.06"W	10	West	23° 9'28.19"N	81°55'34.48"W

<tbody>
</tbody>

I am wrecking my brain to figure something out - I definitely cannot pin 70K points on a map manually Advanced appreciation!

 

[shg]

It assumes that moving E/W is traveling along a parallel, and it will break if you cross the poles.

 

[shg]

A little cleanup to work over the poles and across E/W hemispheres:

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Lat​	Long​	Dist​	Dir​	New Lat​	New Long​	​	​
2​	45°00'00.00"N​	90°00'00.00"W​	100​	N​	45°00'00.99"N​	90°00'00.00"W​		E2: =Deg2DMS(DMS2Deg(A2) + CHOOSE(SEARCH(D2, "NESW"), CONVERT(C2, "ft", "m") / 111120, 0, -CONVERT(C2, "ft", "m") / 111120, 0), TRUE)
3​	45°00'00.00"N​	90°00'00.00"W​	100​	E​	45°00'00.00"N​	89°59'58.60"W​		F2: =Deg2DMS(DMS2Deg(B2) + CHOOSE(SEARCH(D2, "NESW"), 0, CONVERT(C2, "ft", "m") / 111120 / COS(RADIANS(DMS2Deg(A2))), 0, -CONVERT(C2, "ft", "m") / 111120 / COS(RADIANS(DMS2Deg(A2)))), FALSE)
4​	45°00'00.00"N​	90°00'00.00"W​	100​	S​	44°59'59.01"N​	90°00'00.00"W​
5​	45°00'00.00"N​	90°00'00.00"W​	100​	W​	45°00'00.00"N​	90°00'01.40"W​

Function DMS2Deg(ByVal s As String) As Double
  ' Converts d° mm' ss.ss{N|E|S|W}" to decimal degrees

  Dim iSgn          As Long

  s = Replace$(s, " ", "")
  Select Case UCase(Right$(s, 1))
    Case "N", "E"
      iSgn = 1
      s = Left$(s, Len(s) - 1)
    Case "S", "W"
      iSgn = -1
      s = Left$(s, Len(s) - 1)
  End Select

  s = Replace$(s, "°", "+")        ' replace the degree sign
  s = Replace$(s, "'", "/60+")     ' replace the minute sign (single quote)
  s = Replace$(s, """", "/3600")   ' replace the second sign (if double quote)
  s = Replace$(s, "''", "/3600")   ' replace the second sign (if two single quotes)
  If Left$(s, 1) = "-" Then
    DMS2Deg = -iSgn * Evaluate(Mid$(s, 2))
  Else
    DMS2Deg = iSgn * Evaluate(s)
  End If
End Function

Function Deg2DMS(ByVal d As Double, bLatLon As Boolean) As String
  ' formats decimal degrees as d°mm'ss.00{N|E|S|W}
  ' bLatLong = True for latitude, False for longitude

  Dim sHem          As String ' hemisphere {N|E|S|W}
  Dim dDeg          As Double
  Dim dMin          As Double
  Dim dSec          As Double

  ' convert to 0 to 360
  d = d - Int(d / 360#) * 360#

  If bLatLon Then
    ' convert lat to +/- 90
    Select Case d
      Case Is <= 90#
        sHem = "N"
      Case Is <= 180#
        d = 180# - d
        sHem = "N"
      Case Is <= 270
        d = d - 180#
        sHem = "S"
      Case Else
        d = 360# - d
        sHem = "S"
    End Select
  Else
    ' convert lon to +/- 180
    Select Case d
      Case Is < 180#
        sHem = "E"
      Case Else
        d = 360# - d
        sHem = "W"
    End Select
  End If

  dDeg = Int(d)
  d = 60# * (d - Int(d))
  dMin = Int(d)
  dSec = 60# * (d - Int(d))
  Deg2DMS = Format(dDeg, "0°") & Format(dMin, "00'") & Format(dSec, "00.00\""") & sHem
End Function

 

[D1XII]

I think this is correct:

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Lat​	Long​	Dist​	Dir​	New Lat​	New Long​
2​	23° 9'28.19"N​	81°55'34.06"W​	10​	N​	23°9'28.29"N​	81°55'34.06"S​		E2: =Deg2DMS(DMS2Deg(A2) + CHOOSE(SEARCH(D2, "NESW"), CONVERT(C2, "ft","m") / 111120, 0, -CONVERT(C2, "ft","m") / 111120, 0), "NS")
3​	23° 9'28.19"N​	81°55'34.06"W​	10​	E​	23°9'28.19"N​	81°55'33.95"S​		F2: =Deg2DMS(DMS2Deg(B2) + CHOOSE(SEARCH(D2, "NESW"), 0, CONVERT(C2, "ft","m") / 111120 / COS(RADIANS(DMS2Deg(A2))), 0, -CONVERT(C2, "ft","m") / 111120 / COS(RADIANS(DMS2Deg(A2)))), "NS")
4​	23° 9'28.19"N​	81°55'34.06"W​	10​	S​	23°9'28.09"N​	81°55'34.06"S​
5​	23° 9'28.19"N​	81°55'34.06"W​	10​	W​	23°9'28.19"N​	81°55'34.17"S​

<tbody>
</tbody>

SHG!! You are amazing! I am going to test this right now. I have been pinning points with a map overlay since I couldn't figure it out. You're a genius! PM me your paypal email - I will gift you for your assistance or send you an amazon card or something at least! Thanks man! I will let you know how this works out. Blessings.

 

[shg]

Let me know how it works out. It could be modified to work on the ellipsoid for movement in cardinal directions.