#### 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.

1 Replies
385 Views

Similar Articles

[PageSpeed] 16

```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.

--
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

Similar Artilces:

Primary partitions
Say I have four hard drives(physical drives) in one computer. Is the four primary partition per each physical hard drive? In theory than I could have 16 primary partitions in a computer that has four physical drives. Is this statement correct? With Microsoft's partitioning that's correct. There are other utilities that will let you do more than four primary partitions. If your installing Win 7 let it do the partitioning. Win 7 has a 100MB partition for system files and then another primary partition for the rest of Win 7. Win 7, as does Vista, you can shrink...

How to Change the Default Directory Location in Outlook
When I was using Win 98 I was able to change Default Directory Location in Outlook following MS knowledge Base item #: Q178156 and make changes in registry. I could not do the same any more now when I am using a XP professional PC. What I wanted to do may apply to many of Outlook users. I wanted all my saved emails to go to a certain holder other then the default "My document" folder. Hope I can get help from someone who have the experience. Thans, Ed ...

two units on primary Y axis
Hay!! I have spent quite some time looking for a solution and I doubt there is one. Is it possible to have two units on primary Y axis such us for example EUR /USD.. For example primary Y would have values such us : 1000 EUR / 1300 USD, 2000 EUR/2600 USD etc.. If there was a way that custom format would include possibility of calculation for unit.. tnx Rado Hi, Perhaps you can create a fake value axis using a dummy data series plotted as xy-scatter. Display data labels which are linked to cells that have the required text. Data for series would be like, X Y Label 0 ...

loading a count of another entity into a field on the primary entity
Hi I have a custom entity associated with Opportunities called Required Conditions. From the Opportunity enity I need to be able to check the count of the Required Conditions in order to disallow/allow certain actions. The simplest way of doing this (from what I can see) would be to populate a field on Opportunity with the count of the Required Conditions when the Opporutnity is loaded, however I have no idea how to do this. Can anyone out there assist? TIA Saira You may want to consider a callout (3.0)/plug-in (4.0) instead. Whenever one of your custom entities is created, updated...

File Location when Signature Created
Office 2003\Outlook, when you create a Signature, where is the file stored? And can it just be copied to another machine.... Thanks in advance... Michael it's in a signature in your local settings path and can be moved. C:\Documents and Settings\username\Application Data\Microsoft -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www....

Email Addresses not being generated for users in secondary domain
I have my exchange 2003 server sitting in a Windows 2003 domain (primary domain) ... however, there is a second domain that is only Windows 2000... when I try to generate mailboxes for accounts in that domain, no recipiant addresses are generated, under the exchange addresses. I'm not sure what to make of this. I can't tell if it's because the domain is Windows 2000, or some other issue. In addition, this same mail server (which is 2003 SP 1) is also having an error every hour. I'm unsure if it's related to the exchange problem or not. All of the information I lo...

Contacts should have both a primary contact and primary account
My customer would like to have both a primary contact and primary account like the account record. ---------------- 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 suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=dcb8b04e-171f-4d5a-8b96-f...

Running an inherited Exchange 2000 server using DNS, no smarthost. My problem is resolved...but I'm trying to understand what happened and why. Here's the events that occured...this morning, I tried to set an email deletion policy in the Recipient Policies container in Exchange. For some reason (still don't know what I did wrong), doing this changed everyone's primary email address to a different one. (for some background...originally this server was setup for the format such as jsmith@domain.com ....recently, they changed the email addresses to the format john.smith@doma...

Locate a specific row or column
Hi all, Could you tell me how to locate a specific row instead of using Mouse to dray the scroll bar? Clara thank you so much for your help In VBA code, you can use: Range("D100").Activate ' Or Range("D1:D100").Select ' Depending on if you want only one cell highlighted or a range of cells. -- Best wishes, Jim "clara" wrote: > Hi all, > > Could you tell me how to locate a specific row instead of using Mouse to > dray the scroll bar? > > Clara > > thank you so much for your help Hi Clara Have you tried goto in the Ed...

Primary Keys
I have a table of data where the primary key is a short code. A number of these codes are grouped together to produce a reporting group. I have now encountered a problem where i have had to set the report name and the short code as primary keys, as i am overlapping on the short codes. The problem is that i can no longer amend data in my report, while in a datasheet view and somebody else is in the db. The table is linked to another table using the 1st primary key as a link Do you know how i can resolve this issue? ...

Do I have to set the primary key to include primary key fields?
I have read that "in a junction table, you need to set the primary key to include the primary key fields from the other two tables. " If I do this, I keep getting the error message "the change you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. The only way I'm entering data is through a form based on a query which automatically writes to all 3 tables (the junction and 2 parents) at once. If I remove the primary keys in the junction tables, I no longer get the error message....

Getting the XPath for the current node (where the carret is located)?
As long as I have tested, Visual Studio cannot show XPathes for selected nodes, right? I have tried many software but few could show XPathes and let me edit the XML in plain text editor(like Visual Studio, not the tree shapes like XML Notepad) at the same time. The only software I found was XML Spy, but it was too expensive for me to buy just to find XPathes. Is there any utility (a separate application or Visual Studio extension) that can show XPath and let me edit the XML in plain text, that is a freeware or a cheap one? Of course I have to search for text. Everyone probably ...

Strange Primary Key values
Hi, I have created a form based on a table using the wizard. This table has a primary key field plus a few more. When I add new rows to the table using the form, I gets strange primary key values such as -298239823. Any ideas why this might be? Thanks, Aine Sounds as though your primary key is an AutoNumber set to Random, rather than Sequential. That shouldn't matter: the value of an AutoNumber key has no meaning. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) <aine_canby@yahoo.com> wrote in message news:1186493404.056219.122550@d55g2000...

Importing e-mails from secondary HDD to primary
I just got a new hard drive and I want to keep the e-mails I had on my old drive. How would I go about doing this? This is Outlook 2003. Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ...

Primary supplier
I have a shop which is supplied by my warehouse. Therefore the warehouse is always set as the primary supplier to the shop. when i run a sales report for the shop by supplier i obviously get 100% of sales from the warehouse. However i would like to run a sales report by the real ie secondary supplier. Any help here would be gratefully accepted. thanks If you're not using BIN LOCATION you could use this field to track the real supplier (if you're using the detailed sales report its the only available field for this purpose); is there any reason why you aren't doing transfers...

Cannot write to memory location
I can not (all of the sudden) run Publisher in the last week. When starting to run it, I get that it cannot write to a certain memory locaiton, so it is shutting down. I need to get this program working. I have read through this forum and tried the different fixes as well as the knowledge base (emptied the temp file, disabled anti-virus, etc.) but it still won't work. An example is: "The instruction at "0x77f585c0" referenced memory at "0x00000000". the memory could not be "written". Click on OK to terminate the program." The header read...

How do i remove old server locations in meeting workspace?
I have a situation where our workspace server location has changed its url. A lot of users have simply created a new server location using the updated URL but some users are still confused when accidently trying to create a workspace using the outdated URL. Is it possible to clear the list of server locations or at least remove the outdated location? ...

Primary Catagory
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel How do you set a catagory ss primary so that a new event automatically is assigned to that catagory? ATM, mine comes up as &quot;None&quot; <br> When in the &quot;assign catagories&quot; section there is a button on bottom left which is &quot;Set Primary&quot; which I imagine is what I want but it is grey and cannot be used. <br><br>Also, does Entourage have a manual? So that I can find these things out for myself rather than have to search this forum or watch their s...

Assign data series to primary axis
Line chart: Data Series: Altitude Oil Temp Oil Temp uses the primary vertical axis and Altitude the secondary. I want to add OAT (outside air temp), plotted against the primary axis. But when I add the new data series it uses the secondary axis and plots itself as a nearly straight horizontal line along the bottom of the chart. In this position I cannot seem to 'select' the series with the cursor. How can I add the OAT data series plotted against the primary axis. I have just upgraded to Excel 2007. -- Art Right click the added series, choose Format. In a visible position, I ...

upgrading a "secondary" PST file to Outlook 2003 format
I have a "secondary" Outlook PST file that I use only some of the time. I just noticed that it is still in Outlook 97-2002 format, and I want to upgrade it to Outlook 2003 format. How do I do that. Some time ago, Outlook prompted me to convert my main PST file to 2003 format, but I can't find a way to convert other PST files. I searched the MS Knowledge Base but could find nothing of help. Thanks, I Live On Outlook F1 help will be your guide. "Convert to unicode .pst" --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact...

What is a primary key?
If someone can please explain what a primary key in access is, I would appreciate it. Also in a database that has multiple tables, are there multiple primary keys? Thanks for helping. Obviously I'm a newbie. A primary key (PK) is a unique index on a table. Every table should have one. If you have multiple tables, each one should have its own PK. A database sorts its records in the way you specify in your query. So if a table has 2 identical rows, and you ask to update one of them, which one gets changed? The answer is not properly defined. Therefore every table should have some wa...

Primary Key names. (with random ?? numbers in them).
Dear reader, For primairy key names I was (and still am) used to names like : 1. PK_<table_name> Now I have a database which has primairy keys with double underscores and names like : 2. PK__<table_name>__<number> There are two underscores and a large number. When scripting (database or table): The name from 1 appears in the script. The name from 2 does not appear in the script and a similar name with a different number is generated when the script is executed. My problem, if I do not have control over the names of objects, it becomes difficult to ma...

Primary Key
Hi To anyone who can help.. I have been designing a small database for someone who wants to keep track of Training Courses booked. While setting this up I have been using sample data so that now my booking number starts at 37 instead of 1. When I give this database over to the person that will be using it I want it to start counting again at 1 - is this possible? Many thanks Ann Hi Ann sounds you're not in the correct NG. sound more like a MS Access topic? -- Regards Frank Kabel Frankfurt, Germany Ann wrote: > Hi > > To anyone who can help.. > > I have been ...

Location of CRM data file
Could someone please tell me where the Contact Manager data file (not .pst) is located by default. It would also be good to know how I can change its' location if that is possible. Thank you Ian ...

primary key collisions due to auto reseeding of auto number field.
has anyone come across this problem in Access2003 Can you be more specific, Simon? Is the AutoNumber being seeded intentionally? Or is it one of the causes listed at the top of this article: Fixing AutoNumbers when Access assigns negatives or duplicates at: http://allenbrowne.com/ser-40.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "simcon" <simcon@discussions.microsoft.com> wrote in message news:0AFF03CF-F2B6-4312-ABA3-9DF036466FBD@m...