Ok everybody this is my first post and I am completely stumped!
I work for a company where we track our employees times by using gps time stamps through an iphone application. Here is the way I invision the spreadsheet working.
The Meta data from the gps pings from the employees phones comes in as lat, and long points that gets exported from the application into an excel spreadsheet along with User name
We have a list of Jobs that I also have a meta data for that I have geo coded using the address of the job to get a latitude and longitude coordinate.
Luckily I found a formula for finding the distance between two different sets of coordinates that calculates how much distance between the two sets of coordinates is.
I am trying to build a geo fencing capability with this data.
In a nutshell here is what I need to do hopefully all in one Macro or Array formula.
On Page 1 I have gps coordinates of every Job location we have. I already have the formula for the distance between Job coordinates and cell phone ping coordinates down. My issue is that For every ping i need a formula or macro that is going to reference the distance between that individual ping and EVERY set of job coordinates in the list on page 1 by looping from the first set of coordinates down until it reaches the very last set of coordinates on the list. Once all of these are computed if any of the distances are less than or equal to 500ft than I want the Job number of that set of coordinates to be listed as the in the cell next to the GPS ping in a column labeled "location". If there is no match than I want the answer to come back as "Travel".
What I need is for this to happen all in one macro that I can paste into the cell next to the first GPS Ping coordinates and then copy that formula all the way down page 3 until it hits the last set of coordinates. I have tried and tried and tried and cannot figure out how to get all of this into one formula without writing the worlds largest array formula
What I have so far is as follows:
First formula:=GreatCircleDistance($C$5,$D$5,C8,D8) (The GreatCircleDistance is a formula that measures the distance I found on the web)
(This is the formula I need looped from c8,d8 until end of column c and d to get all distances from ping to business location)
Second Formula: =IF(($H$100)<0.6,B100,LOOKUP(MAX(1000)*MIN(1),$G$5:$G$1222,$B$5:$B$2462))
Once all of the jobs have been referrenced and the distances have been populated I want to do a search of the indexed distances to check if any of the distances are smaller than 1000 ft. If they are I want all of the answers that may populate to populate seperated by a comma (if more than one location is less that 1,000 feet) the first part of the equation is for the main office here. We work off a 800 acre farm so the distance had to be a little bit bigger than 1000 ft. everything else 1000 ft would be fine.
I have the way its working now completed but it is not what I need and would take FOREVER to do this process over and over again. I attached some pictures as well to show you what I am dealing with and I am praying someone can help!!!!!!
Thanks ahead of time
I work for a company where we track our employees times by using gps time stamps through an iphone application. Here is the way I invision the spreadsheet working.
The Meta data from the gps pings from the employees phones comes in as lat, and long points that gets exported from the application into an excel spreadsheet along with User name
We have a list of Jobs that I also have a meta data for that I have geo coded using the address of the job to get a latitude and longitude coordinate.
Luckily I found a formula for finding the distance between two different sets of coordinates that calculates how much distance between the two sets of coordinates is.
I am trying to build a geo fencing capability with this data.
In a nutshell here is what I need to do hopefully all in one Macro or Array formula.
On Page 1 I have gps coordinates of every Job location we have. I already have the formula for the distance between Job coordinates and cell phone ping coordinates down. My issue is that For every ping i need a formula or macro that is going to reference the distance between that individual ping and EVERY set of job coordinates in the list on page 1 by looping from the first set of coordinates down until it reaches the very last set of coordinates on the list. Once all of these are computed if any of the distances are less than or equal to 500ft than I want the Job number of that set of coordinates to be listed as the in the cell next to the GPS ping in a column labeled "location". If there is no match than I want the answer to come back as "Travel".
What I need is for this to happen all in one macro that I can paste into the cell next to the first GPS Ping coordinates and then copy that formula all the way down page 3 until it hits the last set of coordinates. I have tried and tried and tried and cannot figure out how to get all of this into one formula without writing the worlds largest array formula
What I have so far is as follows:
First formula:=GreatCircleDistance($C$5,$D$5,C8,D8) (The GreatCircleDistance is a formula that measures the distance I found on the web)
(This is the formula I need looped from c8,d8 until end of column c and d to get all distances from ping to business location)
Second Formula: =IF(($H$100)<0.6,B100,LOOKUP(MAX(1000)*MIN(1),$G$5:$G$1222,$B$5:$B$2462))
Once all of the jobs have been referrenced and the distances have been populated I want to do a search of the indexed distances to check if any of the distances are smaller than 1000 ft. If they are I want all of the answers that may populate to populate seperated by a comma (if more than one location is less that 1,000 feet) the first part of the equation is for the main office here. We work off a 800 acre farm so the distance had to be a little bit bigger than 1000 ft. everything else 1000 ft would be fine.
I have the way its working now completed but it is not what I need and would take FOREVER to do this process over and over again. I attached some pictures as well to show you what I am dealing with and I am praying someone can help!!!!!!
Thanks ahead of time
