Primary and Secondary tech locator

Hi, I use a 3rd party excel plug in called Spheresoft that I use to calculate 
distance in zip codes. My question is really a function of excel and not the 
tool itself.
In short I have in Sheet1 a list of sites: address, city, state, zip etc.
I have a sheet called techs that list all the name and addresses of my techs.
Basically it looks at the zip code from sheet one and picks the one with the 
lowest distance value. Works great.
However..
It would be great if i could have column c be the second closest tech. 
Is there a way to have it exclude the tech from column b (which is the 
closest), for the purpose of choosing the second closest. 
My formula in column B is as follows.
=INDEX(techs!$H$2:$H$12,MATCH(zipcodesinlistwithindistance(Sheet1!I2,techs!$G$2:$G$12,$M$1),techs!$G$2:$G$12,0))
I2 contains the client zip code
0
Utf
3/11/2010 3:07:01 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
419 Views

Similar Articles

[PageSpeed] 27

If you have some kind of function that returns the actual distances as 
numbers then you can look for the 2nd closest distance. for example:

10
22
25
12
31

10 would be the closest and 12 would be the 2nd closest.  Finding 12 and the 
corresponding tech would be relatively easy if this is how your program 
works. But, you'd need to tell us where all the pertinent information is 
located.

Or, perhaps your progam lists all the zip codes by closest proximity. If so, 
then the function to find the 2nd closest zip would be the same as described 
above.

12345
13452
17654
17777
18029

12345 would be the closest zip. 13452 would be the 2nd closest. It should 
easy to find 13452 and the corresponding tech.

So, it all depends on what your add-in does!

-- 
Biff
Microsoft Excel MVP


"Craig860" <Craig860@discussions.microsoft.com> wrote in message 
news:F4EB1F44-18E7-4B4C-A37D-88207BB9AEB2@microsoft.com...
> Hi, I use a 3rd party excel plug in called Spheresoft that I use to 
> calculate
> distance in zip codes. My question is really a function of excel and not 
> the
> tool itself.
> In short I have in Sheet1 a list of sites: address, city, state, zip etc.
> I have a sheet called techs that list all the name and addresses of my 
> techs.
> Basically it looks at the zip code from sheet one and picks the one with 
> the
> lowest distance value. Works great.
> However..
> It would be great if i could have column c be the second closest tech.
> Is there a way to have it exclude the tech from column b (which is the
> closest), for the purpose of choosing the second closest.
> My formula in column B is as follows.
> =INDEX(techs!$H$2:$H$12,MATCH(zipcodesinlistwithindistance(Sheet1!I2,techs!$G$2:$G$12,$M$1),techs!$G$2:$G$12,0))
> I2 contains the client zip code 


0
T
3/11/2010 5:27:03 AM
Reply:

Similar Artilces:

Getting error when trying to upload large files on ftp location
Hi, I am trying to upload a large xml file (around 500 MB) on ftp location,but i am getting this error--- The underlying connection was closed: An unexpected error occurred on a receive. i am getting the error on this line:- " Stream strm = ftpRequest.GetRequestStream();" i am using the following code:- string upd_file = "ftp://" + FTPHost + "/" + FTPFolder + "/" + "JJH.xml"; FtpWebRequest ftpRequest; FtpWebResponse ftpResponse; FileInfo fileInf = new FileI...

Problem with secondary home pages GPO
I am trying to set and LOCK the users homepage and secondary home page, So when you launch IE it opens 2 tabs, and users can not change them. Seems like there are 3 places in GPO to set the homepage, but only one of them locks them So I have set \windows components\internet explorer\disable changing home page settings to be the home page I want, that works and lock the setting for the 1 home page. Then I also set the "disable changing secondary home page setting" to the 2nd tab I wanted, and this does not work whe I START IE, but it does work if I'm in IE and hit ...

cant access live email tech support non responsive 4th day
my password wont work now im locked out i need some help MICROSOFT IS A MESS ISSUE AFTER ISSUE ,MAC SEEMS MORE INTERESTING IM SO FUSTRATED -- JMorrison23 "JMorrison23" <guest@unknown-email.com> wrote in message news:e2c85fd912a3a66dd6520c9f4b8fdb93@nntp-gateway.com... > > my password wont work now im locked out i need some help MICROSOFT IS A > MESS ISSUE AFTER ISSUE ,MAC SEEMS MORE INTERESTING IM SO FUSTRATED > > > -- > JMorrison23 I don't believe that Microsoft is at fault for your being unable to logon. I suggest you sell...

Change default file location?
I have been looking , unsuccessfully, to figure out how to change the location of my outlook .pst files from the default C:\ Docs & Set, etc. location to my D: drive. This is the only Office XP component that I have not been able to get moved to a more "common" area. Could someone please tell me how to get this accomplished? I am running Office XP on Windows XP Pro, SP2 Thanks for the help. You can use the ForcePSTPath registry value to force the creation of new PST files to an alternate location. http://support.microsoft.com/kb/883401 If memory serves me right, this v...

Secondary Axis on Line Chart
Hi Guys, I posted this a week ago, but it seems to have vanished from the board! Is it possible to plot two data sets on the same line graph with different scales? i.e. Have a secondary axis, like in Excel? I have one dataset fluctuating around 2.0 over time and another dataset fluctuating around 50 over time. If I plot both of these in the same graph, I lose the subtle changes in the first dataset. I can sort of bodge it by writing a query with an expression to multiply the first dataset by 25, but then I can't get the "real" values to show on the axis. I find the graph funct...

Reporting with multiple locations (site id's)
Hello, I have a report which pull Inventory Tx from all sites (70, 72, 80, 82, 91, 92, etc.) which uses the [Item Number] as the input parameter, works fine. Now, I need to have the same report but pull certain columns from other locations, which I'm finding is breaking the design of the report on the database side. For instance, the QTY B/O needs to pull from Site 82, the QTY AVAIL needs to pull from Sites 72 and 82, and the QTY ON PO needs to pull from Site 72. I am currently using a SQL Server 2000 stored procedure which uses several views to pull the results, and have tried many diffe...

Drop down arrow location for validated data
I have created a large database of rating bullets for students at military academy. They are all fairly long and wordy. I want to mov the arrow from the right side to the left side so it can be see without having to scroll right to the end of the cell. Is thi possible with a list of validated data? Is there any other way that can do this without the validated lists -- Message posted from http://www.ExcelForum.com You can't change the position of the dropdown arrow in a cell with data validation. You could make the column narrower, and use code to temporarily widen it when the cel...

Move to Previous Location
Hi, Curser was in page1 of a document. I scrolled to page 5 and selected an item in page 5. Now I want to go to previous location. Is there a shortcut for doing this? Thanks! GoBack (Shift+F5). GoBack will skip back to the previous 5 edit points. -- Terry Farrell - MSWord MVP "Maanu" <Maanu@discussions.microsoft.com> wrote in message news:4B07034B-78EC-4B09-B015-6B7DFCF625E9@microsoft.com... > Hi, > > Curser was in page1 of a document. I scrolled to page 5 and selected an > item > in page 5. Now I want to go to previous location. >...

Trusted Locations
Hello, I have an Access 2002 database that is distributed to users operating mostly in a runtime version and do not have the full version of Access. I would like to convert to Access 2007 but the problem that I'm running into is how to deal with the "Trusted Location" in a runtime environment. What is the generally accepted method for assigning the Trusted Location in a runtime environment? Remember these are novice users and I cannot ask them to make registry modifications. Thanks, Vic On Sep 5, 4:37 am, "vic" <v...@showsec.com> wrote: See http://...

E-mail to Primary Contact of Account
I use accounts to represent both Companies and Individual clients. We are a mortage broker so I my client is Ken and Julie Smith, the account is Smith, Ken and Julie and there are two contact records, one for Ken and one for Julie. One of these contacts is the primary contact and I do not store the e-mail address at the account level. I want to send an e-mail to the account and assumed that it would look to the primary contact for the e-mail address. Is this correct? If not can I have it do this? Seems kinda stupid to have primary contact information but then have to duplicate th...

how to locate where an xslt stylesheet failed
Dear experts, After converting some .net code from using XslTransform to XslCompiledTransform, one of my XSLs no longer runs. it fails with: Attribute and namespace nodes cannot be added to the parent element after a text, comment, pi, or sub-element node has already been added. trouble is my stylesheet is long and complicated and I don't know where the error was. is there any way of finding out which line number in the XSLT produced the error? I tried it on Xml spy (which does tell you where the error was) but the same transform doesn't fail in xml spy. Andy Andy Fish wrot...

Outlook 2003: Where is the PST file located?
My Windows XP is hosed and I have to re-install everything. I would like to get my Outlook folders off of the hard drive before I reformat it. I looked for .pst and .pab files, but did not find any. Where are these files located in this version of Outlook and what file names should I be looking for? Make sure you include hidden and system files and folders when searching for pst-file. By default it is located in C:\Documents and Settings\%username%\Local Settings\Application Data\Microsoft\Outlook\ You can also locate the file by using Rightclick the root folder (probably Outlook T...

how do I take over Groove workspace from previous tech manager
ur Groove workspace was installed and managed by a tech who is now no longer working at our company. How can we: - Backup Groove data - Upgrade Participant user (now current owner) to Manager - Change Groove workspace security - Lock out/delete previous manager Tron Try and post this over to the groove newsgroup where all the groove experts are On the web: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.groove -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will n...

Move appts from converted PST to primary PST?
I am doing a conversion on one computer, the outcome of which will be a lot of appointments, contacts, and tasks in a PST file. After the conversion is complete I need the converted data to be all or part of the user's data on the user's computer. Shall we somehow make the conversion file be the primary one, or shall we move appts, contacts, todos into it, or ???, and if so, how best shall we do that? So far we understand how to move contacts and how to move tasks, but with appointments we only see a limited set at a time--one month's worth probably is the largest we see....

Can you have hyperlinks to more than one location per text box?
Hello, Is it possible to have hyperlinks to more than one location per text box? At the moment, I'm finding that if the sentences or paragraphs in which I have hyperlinks are in the same text box, only one location can be specified for all hyperlinks. Whichever hyperlink location I specify for one of the hyperlinks will be copied over to the other hyperlinks. The hyperlinks are to other pages within the same document. To have more than 1 hyperlink location in a sentence or paragraph, I have to create a separate text box for each sentence or paragraph. Is there another way of doing...

Change the Primary Key Value of a Record
I am wanting to change the Primary Key value for data entries after it has already been set. I have one main table (tblManpower) that has every employee in the Facility, with their clock number as the Primary Key. Linked to tblManpower are two other tables (tblDutyHistory and tblPositionHistory). tblManpower will only have one entry per clock number while the other two tables will have multiple entries per clock number. So if the employee ever changes clock numbers, I'd like to be able to change their clock number entries for all the tables. Is there any way to do that? I've trie...

Auto Update Cell location?
I have 2 worksheet which has hyperlinks that simply point to each other. I need to insert an additional row at the top at sheet1. Clicking hyperlink on sheet 1 will still correctly "jump" to the correct cells at sheet2, but not the other way round With hundreds of links, is there any way to auto update the change cell location? ...

delete store location
i have a store that has 2 locations. 1 was deleted, and all the information for that loction is gone. Is there anyway to retrieve the information from that location? ...

Trusted location 08-31-09
I have several SPLIT databases. Currently I have a SHORTCUT for each database that a user runs. It is a .bat that copies the FE from the server to the C:\ drive of the users computer and then opens the database. I did it this way so that I wouldnt have to constantly send out new FEs to all the users. My question is....Do I really have to go to each individual users PC and set their trusted location so that the FE will open without delay and without all the security stuff? Between the 8 databases being used, Im talking 100+ users....is there not a way to do this programmatically? I mean, s...

Sell item from one location and ship from another
RMS Store Operations should have the ability to sell an item from one store and have it shipped from a different store or warehouse and report all 'type' sales to Headquarters in order to have inventory updated while keeping the sale at the original sale location. -- Reed/Daum Associates, Inc. Pittsburgh PA ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the sugges...

Required Tech support(Exchange down)
Hi Microsoft! Please give me better suggestion that I m Exchange manager of my entire organization. My company is using Ms Exchange 2003. I m facing problem regarding incoming mails. When my Exchange down due to any reason (Power failure, disconnection, exchange down etc). my emails are return back to senders. These emails are not queued in my main ISP server’s Secondary DNS. My main DNS server(ISP) is not be able to store these emails temporarily. Kindly suggests me best solution Please look into the problem and advise me solution/remedy for the said problem. Your earl...

Mass Primary supplier change
This problem has me stumped, I've played around with the inventory wizard but still cant achieve what I want. At the moment I've got about 400 products (all their descriptions start with the same 4 letters eg. ABCD) and all the products have 3 suppliers setup (Supplier A, Supplier B & Supplier C). Problem is, that at the moment the PRIMARY supplier is Supplier A, but I need to change this for all 400 products to Supplier B. Is there any way I can avoid having make this change manually 400 times? Cheers You will need to use SQL queries to do this one. Be sure to take a backup...

PRIMARY KEY violation!
Hello, I just changed my server (hence my SQL Server 2005 to another machine), everything went very well execpt that when one of the users was posting a transaction containing an account with an analysis, i get an error that there is a violation of the PRIMARY KEY constraint PKAAG10000. cannot insert duplicate key in object dbo.AAG10000. as a result, the amount of the transaction is not found in the cost center according to the analysis. any clue? Thank You, Fadi Tawakol You will need to see what fields are in the primary key for table AAG10000. I don't have Analytical Analysis so I ...

IMAP default delivery location (Outlook)
Hi: I have my AOL account mapped into my Outlook 2003 via IMAP. Under "E-mail Accounts" i have the "Deliver new e-mail to the following location" set to a PST file's inbox location. But, new AOL mail always comes into the IMAP Inbox, not the PST file specified as above. What am I'm not understanding about this? I want all new mail to come to one Inbox, the one in my personal PST file. thanks, russ You need to create a rule that moves all messages received in your IMAP Inbox to your PST. IMAP doesn't care about the deliver new e-mail to the following lo...

download from bank so quick can't see a location
During download from bank to money, the download is so quick that I can't see where it saves on my computer. In microsoft.public.money, Lottie wrote: >During download from bank to money, the download is so >quick that I can't see where it saves on my computer. Start->Settings->ControlPanel->FolderOptions->FileTypes Select OFX-- Open Financial Exchange File (if that is the file type you are downloading). Click Advanced. Check Confirm Open After Download. You will then get pop-up box to allow you to save the file where you wish. You can also click Open to feed i...