Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Matching Zipcode Range for Address
Hi,
I need some help finding a way to map a zipcode to a sales rep, based on a match within a zipcode range assignment. My lookup table has a ZipLow and ZipHigh value, with a Rep Id assigned to the zipcode range. The zips are both US and Canadian Zip ranges I have a 2nd worksheet that lists a specific address, and I want to compare the zip of the address listed on this worksheet to see which rep would be assigned based on the lookup table below. Sample lookup table: RepID ZipLow ZipHigh Country Mark 33301 33999 US Jim 34401 34999 US Sam 35501 35999 US Jill 47701 47999 US Sue 48222 48999 US Amy 60001 60999 US John K0A 0A0 K9Z 9Z9 Canada Jay L0A 0A0 P9Z 9Z9 Canada Sample Worksheet that I need to populate with appropriate RepID: Address/Zip Rep Assignment 60101 Amy 47801 Jill P9Z 9Z6 Jay I tried using Vlookup function with approximate match set with TRUE. It did not work correctly in all cases. Any suggestions???? Thanks so much, Luce |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Matching Zipcode Range for Address
I have tried out the below in the same sheet itself. Assuming the below data
lies in A1:D9 with headers in row1.. F2 = 60101 F3 = P9Z 9Z6 Enter the below formula in G2. Please try and feedback =INDEX($A$1:$A$25,SUMPRODUCT(--($B$1:$B$25<=F2),--($C$1:$C$25=F2),--ROW($B$1:$B$25)),1) If this post helps click Yes --------------- Jacob Skaria "Luce" wrote: Hi, I need some help finding a way to map a zipcode to a sales rep, based on a match within a zipcode range assignment. My lookup table has a ZipLow and ZipHigh value, with a Rep Id assigned to the zipcode range. The zips are both US and Canadian Zip ranges I have a 2nd worksheet that lists a specific address, and I want to compare the zip of the address listed on this worksheet to see which rep would be assigned based on the lookup table below. Sample lookup table: RepID ZipLow ZipHigh Country Mark 33301 33999 US Jim 34401 34999 US Sam 35501 35999 US Jill 47701 47999 US Sue 48222 48999 US Amy 60001 60999 US John K0A 0A0 K9Z 9Z9 Canada Jay L0A 0A0 P9Z 9Z9 Canada Sample Worksheet that I need to populate with appropriate RepID: Address/Zip Rep Assignment 60101 Amy 47801 Jill P9Z 9Z6 Jay I tried using Vlookup function with approximate match set with TRUE. It did not work correctly in all cases. Any suggestions???? Thanks so much, Luce |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Matching Zipcode Range for Address
This formula works great! Very interesting approach!
Thank you! "Jacob Skaria" wrote: I have tried out the below in the same sheet itself. Assuming the below data lies in A1:D9 with headers in row1.. F2 = 60101 F3 = P9Z 9Z6 Enter the below formula in G2. Please try and feedback =INDEX($A$1:$A$25,SUMPRODUCT(--($B$1:$B$25<=F2),--($C$1:$C$25=F2),--ROW($B$1:$B$25)),1) If this post helps click Yes --------------- Jacob Skaria "Luce" wrote: Hi, I need some help finding a way to map a zipcode to a sales rep, based on a match within a zipcode range assignment. My lookup table has a ZipLow and ZipHigh value, with a Rep Id assigned to the zipcode range. The zips are both US and Canadian Zip ranges I have a 2nd worksheet that lists a specific address, and I want to compare the zip of the address listed on this worksheet to see which rep would be assigned based on the lookup table below. Sample lookup table: RepID ZipLow ZipHigh Country Mark 33301 33999 US Jim 34401 34999 US Sam 35501 35999 US Jill 47701 47999 US Sue 48222 48999 US Amy 60001 60999 US John K0A 0A0 K9Z 9Z9 Canada Jay L0A 0A0 P9Z 9Z9 Canada Sample Worksheet that I need to populate with appropriate RepID: Address/Zip Rep Assignment 60101 Amy 47801 Jill P9Z 9Z6 Jay I tried using Vlookup function with approximate match set with TRUE. It did not work correctly in all cases. Any suggestions???? Thanks so much, Luce |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Matching Zipcode Range for Address
The code below actually gets the loacation where the zip codes are located by
going to the US post office website. I can also write a program that wil find the zip code from the address (never need this feature). Sub PostCodes() POSTCODE = InputBox("Enter PostCode (NW12 9BN): ") If POSTCODE = "" Then POSTCODE = "NW12 9BN" End If PHONENUMBER = InputBox("Enter Phone Number (02071234567): ") If PHONENUMBER = "" Then PHONENUMBER = "02071234567" End If Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "http://www.orange.co.uk/partner-savc/default.cfm" 'get web page IE.Navigate2 URL Do While IE.readyState < 4 DoEvents Loop Do While IE.busy = True DoEvents Loop Set Form = IE.document.getElementsByTagname("Form") Set Inputform = Form.Item(0) Set PhoneNumbox = Inputform.Item(0) PhoneNumbox.Value = PHONENUMBER Set PostCodebox = Inputform.Item(1) PostCodebox.Value = POSTCODE Set POSTCODEbutton = IE.document.getElementById("theForm") POSTCODEbutton.onsubmit = POSTCODEbutton.action POSTCODEbutton.submit 'POSTCODEbutton.Click Do While IE.busy = True DoEvents Loop 'Set Table = IE.document.getElementsByTagname("Table") 'Location = Table(0).Rows(2).innertext IE.Quit 'MsgBox ("Zip code = " & POSTCODE & " City/State = " & Location) End Sub "Luce" wrote: This formula works great! Very interesting approach! Thank you! "Jacob Skaria" wrote: I have tried out the below in the same sheet itself. Assuming the below data lies in A1:D9 with headers in row1.. F2 = 60101 F3 = P9Z 9Z6 Enter the below formula in G2. Please try and feedback =INDEX($A$1:$A$25,SUMPRODUCT(--($B$1:$B$25<=F2),--($C$1:$C$25=F2),--ROW($B$1:$B$25)),1) If this post helps click Yes --------------- Jacob Skaria "Luce" wrote: Hi, I need some help finding a way to map a zipcode to a sales rep, based on a match within a zipcode range assignment. My lookup table has a ZipLow and ZipHigh value, with a Rep Id assigned to the zipcode range. The zips are both US and Canadian Zip ranges I have a 2nd worksheet that lists a specific address, and I want to compare the zip of the address listed on this worksheet to see which rep would be assigned based on the lookup table below. Sample lookup table: RepID ZipLow ZipHigh Country Mark 33301 33999 US Jim 34401 34999 US Sam 35501 35999 US Jill 47701 47999 US Sue 48222 48999 US Amy 60001 60999 US John K0A 0A0 K9Z 9Z9 Canada Jay L0A 0A0 P9Z 9Z9 Canada Sample Worksheet that I need to populate with appropriate RepID: Address/Zip Rep Assignment 60101 Amy 47801 Jill P9Z 9Z6 Jay I tried using Vlookup function with approximate match set with TRUE. It did not work correctly in all cases. Any suggestions???? Thanks so much, Luce |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Address of merged range VBA | Excel Discussion (Misc queries) | |||
How to Find the Column address from a Range Dimensioned Variable | Excel Programming | |||
Find %ontime & SUMIF ontime ie: find matching sets within Range... | Excel Worksheet Functions | |||
How to find the address of the min cell within a range | Excel Worksheet Functions | |||
A function to find a phrase within a range of cells and return the address of the first instance. | Excel Programming |