Marco similar to a SAS program
 

Hi,

I am not sure where else to ask but this seems like the place.

My job uses Office 2000. Let the groaning commence. What I am trying to do
is create a Macro in Excel 2000 that duplicates what a macro in SAS does?

The macro in SAS takes the Longitude and Latitude for a business, goes
through a list of all our store location long and lat and figures the
distance, then keeps the minimun distance.

There are about 1020 Store locations. The number of Business is currently
10 though that number could change depending on sales.

SAS works great for things like this. The idea is to get it into Excel and
then let the department requesting the information do it on their own.

Any advice would be greatly.

Thanks
Christian Falde

Marco similar to a SAS program
 

Excel would have no problem doing this.

Depending on the output you'd like you could approach it a couple different
ways. Are the 10 businesses always going to be the same?

I would set up all your store locations with long/lat on one sheet and all
your business locations with long/lat on another sheet. Define a dynamic
range for the business locations and then use a For Each loop to go through
each location.

Rather than looping through all the store locations I would feed the current
bus location into long/lat cells on the store location sheet and set up
forumlas to determine the distance from a to b using pythagorean. Each time
you loop to a new business location you would reset the long/lat of the
business location on the store location sheet. The formulas will recalculate
and then sort you should use .sort to find the shortest distance




"Christian Falde" wrote:

Hi,

I am not sure where else to ask but this seems like the place.

My job uses Office 2000. Let the groaning commence. What I am trying to do
is create a Macro in Excel 2000 that duplicates what a macro in SAS does?

The macro in SAS takes the Longitude and Latitude for a business, goes
through a list of all our store location long and lat and figures the
distance, then keeps the minimun distance.

There are about 1020 Store locations. The number of Business is currently
10 though that number could change depending on sales.

SAS works great for things like this. The idea is to get it into Excel and
then let the department requesting the information do it on their own.

Any advice would be greatly.

Thanks
Christian Falde

Marco similar to a SAS program
 

In column A I would put the store name or id.
In column B the lattitude
In column C the longitude

Pick a business
Fill column D with the business lattitude (all the same)
Fill column E with the business longitude (all the same)

Fill column F with the distance formula found in:

http://www.cpearson.com/excel/latlong.htm

Finally sort cols A thru F by F descending.
The top entry will be the closest.

Repeat for the next business, etc.
--
Gary''s Student - gsnu200810


"Christian Falde" wrote:

Hi,

I am not sure where else to ask but this seems like the place.

My job uses Office 2000. Let the groaning commence. What I am trying to do
is create a Macro in Excel 2000 that duplicates what a macro in SAS does?

The macro in SAS takes the Longitude and Latitude for a business, goes
through a list of all our store location long and lat and figures the
distance, then keeps the minimun distance.

There are about 1020 Store locations. The number of Business is currently
10 though that number could change depending on sales.

SAS works great for things like this. The idea is to get it into Excel and
then let the department requesting the information do it on their own.

Any advice would be greatly.

Thanks
Christian Falde