Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with vlookup on uk postcodes
Hello all,
Am a bit stuck here, probably partly because its a friday afternoon... Basically i am making a price calculator which works out how much it will cost to deliver to a postcode input by the user. There are 3 different price zones, and I have a list of which price zones go with which postal areas and districts. (NB: A UK postcode consists of the following format: CV22 2JF. the "CV" is the postal area, "cv21" is the postal district) The problem is that not all the postal areas are completely in one zone, so we have to look at the postal district - ie. instead of just looking for "PE" postal area it needs to look for PE1 or PE17 etc. Thats where the problem comes in. I have already spent ages working out how to make it find only the leading letters at the start by testing each letter consecutively to see if its a number with the code: =IF(ISNUMBER(VALUE(MID(D5,2,1))),LEFT(D5,1),IF(ISN UMBER(VALUE(MID(D5,3,1))),LEFT(D5,2),IF(ISNUMBER(V ALUE(MID(D5,3,1))),LEFT(D5,3),LEFT(D5,4)))) That works ok, another cell now looks at whether its an N/A error or not, and if it is tries including the some numbers as well: =IF(ISNA(IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))),IF(ISNUMBER(VALUE(MID(postcod e,4,1))),VLOOKUP(LEFT(postcode,4),'Zones data'!A1:C359,2,0),VLOOKUP(LEFT(postcode,3),'Zones data'!A1:C293,2,0)),IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))) Thing is, its not working on the postcodes which should be using the postal district because that is a split area. e.g. the postcode PE17 xyz should be identified as a zone two according to the table of zones data: Postcode start Zone .... CV 3 PE14 1 PE15 1 PE16 1 PE17 2 ....etc... But anything that requires numbers as part of the search is turning up a N/A error and I am too tired to follow my own formula now and work out where i am going wrong. Please help with possibly the longest question of all time! Thanks, Oli |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with vlookup on uk postcodes
Hi Oli
to get the Postal District just use =LEFT(A1,FIND(" ",A1)-1) To get the Zone use =MID(A1,FIND(" ",A1)+1,1) -- Regards Roger Govier "oli merge" wrote in message ... Hello all, Am a bit stuck here, probably partly because its a friday afternoon... Basically i am making a price calculator which works out how much it will cost to deliver to a postcode input by the user. There are 3 different price zones, and I have a list of which price zones go with which postal areas and districts. (NB: A UK postcode consists of the following format: CV22 2JF. the "CV" is the postal area, "cv21" is the postal district) The problem is that not all the postal areas are completely in one zone, so we have to look at the postal district - ie. instead of just looking for "PE" postal area it needs to look for PE1 or PE17 etc. Thats where the problem comes in. I have already spent ages working out how to make it find only the leading letters at the start by testing each letter consecutively to see if its a number with the code: =IF(ISNUMBER(VALUE(MID(D5,2,1))),LEFT(D5,1),IF(ISN UMBER(VALUE(MID(D5,3,1))),LEFT(D5,2),IF(ISNUMBER(V ALUE(MID(D5,3,1))),LEFT(D5,3),LEFT(D5,4)))) That works ok, another cell now looks at whether its an N/A error or not, and if it is tries including the some numbers as well: =IF(ISNA(IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))),IF(ISNUMBER(VALUE(MID(postcod e,4,1))),VLOOKUP(LEFT(postcode,4),'Zones data'!A1:C359,2,0),VLOOKUP(LEFT(postcode,3),'Zones data'!A1:C293,2,0)),IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))) Thing is, its not working on the postcodes which should be using the postal district because that is a split area. e.g. the postcode PE17 xyz should be identified as a zone two according to the table of zones data: Postcode start Zone ... CV 3 PE14 1 PE15 1 PE16 1 PE17 2 ...etc... But anything that requires numbers as part of the search is turning up a N/A error and I am too tired to follow my own formula now and work out where i am going wrong. Please help with possibly the longest question of all time! Thanks, Oli |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with vlookup on uk postcodes
Sorry , despite the long posting, it's not clear to me what you are
doing/trying to? Are you trying to determine the postal district (and/or district) and then lookup the zone? why is this a problem? (I am UK-based so understand the post code format which includes several variants on the postal district). Perhaps a few examples would help (me certainly!). "oli merge" wrote: Hello all, Am a bit stuck here, probably partly because its a friday afternoon... Basically i am making a price calculator which works out how much it will cost to deliver to a postcode input by the user. There are 3 different price zones, and I have a list of which price zones go with which postal areas and districts. (NB: A UK postcode consists of the following format: CV22 2JF. the "CV" is the postal area, "cv21" is the postal district) The problem is that not all the postal areas are completely in one zone, so we have to look at the postal district - ie. instead of just looking for "PE" postal area it needs to look for PE1 or PE17 etc. Thats where the problem comes in. I have already spent ages working out how to make it find only the leading letters at the start by testing each letter consecutively to see if its a number with the code: =IF(ISNUMBER(VALUE(MID(D5,2,1))),LEFT(D5,1),IF(ISN UMBER(VALUE(MID(D5,3,1))),LEFT(D5,2),IF(ISNUMBER(V ALUE(MID(D5,3,1))),LEFT(D5,3),LEFT(D5,4)))) That works ok, another cell now looks at whether its an N/A error or not, and if it is tries including the some numbers as well: =IF(ISNA(IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))),IF(ISNUMBER(VALUE(MID(postcod e,4,1))),VLOOKUP(LEFT(postcode,4),'Zones data'!A1:C359,2,0),VLOOKUP(LEFT(postcode,3),'Zones data'!A1:C293,2,0)),IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))) Thing is, its not working on the postcodes which should be using the postal district because that is a split area. e.g. the postcode PE17 xyz should be identified as a zone two according to the table of zones data: Postcode start Zone ... CV 3 PE14 1 PE15 1 PE16 1 PE17 2 ...etc... But anything that requires numbers as part of the search is turning up a N/A error and I am too tired to follow my own formula now and work out where i am going wrong. Please help with possibly the longest question of all time! Thanks, Oli |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with vlookup on uk postcodes
Hi,
I would but i cant guareentee that the space will be in there, maybe i will just validate it to make the user have to put the space in. Out of interest anyone know what wud need to do if i wanted to do this without the space? "Roger Govier" wrote: Hi Oli to get the Postal District just use =LEFT(A1,FIND(" ",A1)-1) To get the Zone use =MID(A1,FIND(" ",A1)+1,1) -- Regards Roger Govier "oli merge" wrote in message ... Hello all, Am a bit stuck here, probably partly because its a friday afternoon... Basically i am making a price calculator which works out how much it will cost to deliver to a postcode input by the user. There are 3 different price zones, and I have a list of which price zones go with which postal areas and districts. (NB: A UK postcode consists of the following format: CV22 2JF. the "CV" is the postal area, "cv21" is the postal district) The problem is that not all the postal areas are completely in one zone, so we have to look at the postal district - ie. instead of just looking for "PE" postal area it needs to look for PE1 or PE17 etc. Thats where the problem comes in. I have already spent ages working out how to make it find only the leading letters at the start by testing each letter consecutively to see if its a number with the code: =IF(ISNUMBER(VALUE(MID(D5,2,1))),LEFT(D5,1),IF(ISN UMBER(VALUE(MID(D5,3,1))),LEFT(D5,2),IF(ISNUMBER(V ALUE(MID(D5,3,1))),LEFT(D5,3),LEFT(D5,4)))) That works ok, another cell now looks at whether its an N/A error or not, and if it is tries including the some numbers as well: =IF(ISNA(IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))),IF(ISNUMBER(VALUE(MID(postcod e,4,1))),VLOOKUP(LEFT(postcode,4),'Zones data'!A1:C359,2,0),VLOOKUP(LEFT(postcode,3),'Zones data'!A1:C293,2,0)),IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))) Thing is, its not working on the postcodes which should be using the postal district because that is a split area. e.g. the postcode PE17 xyz should be identified as a zone two according to the table of zones data: Postcode start Zone ... CV 3 PE14 1 PE15 1 PE16 1 PE17 2 ...etc... But anything that requires numbers as part of the search is turning up a N/A error and I am too tired to follow my own formula now and work out where i am going wrong. Please help with possibly the longest question of all time! Thanks, Oli |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with vlookup on uk postcodes
Hi
It makes life much easier, if you force the original input to be correct. If so, then assuming your data entry will be in column A first set up a number of Named formulae with InsertNameDefine Name First Refers to =LEFT($A1,FIND(" ",$A1)-1) Name Last Refers to =RIGHT($A1,3) Name start Refers to =OR(ISTEXT(LEFT(first)),ISTEXT(LEFT(first,2))) Name mid Refers to =OR(ISTEXT(MID(first,3,1)),ISTEXT(MID(first,4,1))) Name end Refers to =AND(ISNUMBER(--(LEFT(last))),ISTEXT(RIGHT(last,2))) Name numbers Refers to =AND(OR(ISNUMBER(--(MID(first,2,1))),ISNUMBER(--(MID(first,2,2))), ISNUMBER(--(MID(first,3,1))),ISNUMBER(--(MID(first,3,2)))), NOT(ISNUMBER(--(RIGHT(first,3))))) Mark the range of cells in column A where you want the users to enter Postcode, DataValidationCustom =AND(start,mid,end,numbers) Remove tick mark from ignore Blank Got to Error Alert tab and ensure there is a tick mark in Show error Alert. Type a message here if you wish. If not for column A, then change all references from A to the relevant column letter. -- Regards Roger Govier "oli merge" wrote in message ... Hi, I would but i cant guareentee that the space will be in there, maybe i will just validate it to make the user have to put the space in. Out of interest anyone know what wud need to do if i wanted to do this without the space? "Roger Govier" wrote: Hi Oli to get the Postal District just use =LEFT(A1,FIND(" ",A1)-1) To get the Zone use =MID(A1,FIND(" ",A1)+1,1) -- Regards Roger Govier "oli merge" wrote in message ... Hello all, Am a bit stuck here, probably partly because its a friday afternoon... Basically i am making a price calculator which works out how much it will cost to deliver to a postcode input by the user. There are 3 different price zones, and I have a list of which price zones go with which postal areas and districts. (NB: A UK postcode consists of the following format: CV22 2JF. the "CV" is the postal area, "cv21" is the postal district) The problem is that not all the postal areas are completely in one zone, so we have to look at the postal district - ie. instead of just looking for "PE" postal area it needs to look for PE1 or PE17 etc. Thats where the problem comes in. I have already spent ages working out how to make it find only the leading letters at the start by testing each letter consecutively to see if its a number with the code: =IF(ISNUMBER(VALUE(MID(D5,2,1))),LEFT(D5,1),IF(ISN UMBER(VALUE(MID(D5,3,1))),LEFT(D5,2),IF(ISNUMBER(V ALUE(MID(D5,3,1))),LEFT(D5,3),LEFT(D5,4)))) That works ok, another cell now looks at whether its an N/A error or not, and if it is tries including the some numbers as well: =IF(ISNA(IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))),IF(ISNUMBER(VALUE(MID(postcod e,4,1))),VLOOKUP(LEFT(postcode,4),'Zones data'!A1:C359,2,0),VLOOKUP(LEFT(postcode,3),'Zones data'!A1:C293,2,0)),IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))) Thing is, its not working on the postcodes which should be using the postal district because that is a split area. e.g. the postcode PE17 xyz should be identified as a zone two according to the table of zones data: Postcode start Zone ... CV 3 PE14 1 PE15 1 PE16 1 PE17 2 ...etc... But anything that requires numbers as part of the search is turning up a N/A error and I am too tired to follow my own formula now and work out where i am going wrong. Please help with possibly the longest question of all time! Thanks, Oli |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with vlookup on uk postcodes
Hi Toppers,
The problem I am getting is when I put in a postcodes that doesnt fit entirely within one zone. e.g. PE is split into two zones, lets say PE1-20 is zone 1 and PE21-end is zone 2. Without using the gap to decide how many numbers are to be included with the PE , its tricky to get the vlookup to look at the right amount of numbers from the rest of the postcode. e.g. PE21 is in danger of being mistaken for PE2 the way i was doing it. Also, you can have PE2 1JF which is in danger of being mistaken for PE21 if I dont use the space to select which numbers are part of the postal district. I am a bit more awake today so will have a look at Roger's solution. I also think i might be able to find another way around it by adding a couple of "IF(ISNA(..." formulas around what i am already using. not sure about that tho, will have to test it. Will let you know what solution I decide to use, thanks for your help everyone. "Toppers" wrote: Sorry , despite the long posting, it's not clear to me what you are doing/trying to? Are you trying to determine the postal district (and/or district) and then lookup the zone? why is this a problem? (I am UK-based so understand the post code format which includes several variants on the postal district). Perhaps a few examples would help (me certainly!). "oli merge" wrote: Hello all, Am a bit stuck here, probably partly because its a friday afternoon... Basically i am making a price calculator which works out how much it will cost to deliver to a postcode input by the user. There are 3 different price zones, and I have a list of which price zones go with which postal areas and districts. (NB: A UK postcode consists of the following format: CV22 2JF. the "CV" is the postal area, "cv21" is the postal district) The problem is that not all the postal areas are completely in one zone, so we have to look at the postal district - ie. instead of just looking for "PE" postal area it needs to look for PE1 or PE17 etc. Thats where the problem comes in. I have already spent ages working out how to make it find only the leading letters at the start by testing each letter consecutively to see if its a number with the code: =IF(ISNUMBER(VALUE(MID(D5,2,1))),LEFT(D5,1),IF(ISN UMBER(VALUE(MID(D5,3,1))),LEFT(D5,2),IF(ISNUMBER(V ALUE(MID(D5,3,1))),LEFT(D5,3),LEFT(D5,4)))) That works ok, another cell now looks at whether its an N/A error or not, and if it is tries including the some numbers as well: =IF(ISNA(IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))),IF(ISNUMBER(VALUE(MID(postcod e,4,1))),VLOOKUP(LEFT(postcode,4),'Zones data'!A1:C359,2,0),VLOOKUP(LEFT(postcode,3),'Zones data'!A1:C293,2,0)),IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))) Thing is, its not working on the postcodes which should be using the postal district because that is a split area. e.g. the postcode PE17 xyz should be identified as a zone two according to the table of zones data: Postcode start Zone ... CV 3 PE14 1 PE15 1 PE16 1 PE17 2 ...etc... But anything that requires numbers as part of the search is turning up a N/A error and I am too tired to follow my own formula now and work out where i am going wrong. Please help with possibly the longest question of all time! Thanks, Oli |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with vlookup on uk postcodes
strangely it seems i have already done this and it has started working again.
Im sure im using the same test data as last week, but now im getting the correct answers for the split postcodes. perhaps it only errors on certain postcodes, I will have to check, Will let you all know. "oli merge" wrote: Hi Toppers, The problem I am getting is when I put in a postcodes that doesnt fit entirely within one zone. e.g. PE is split into two zones, lets say PE1-20 is zone 1 and PE21-end is zone 2. Without using the gap to decide how many numbers are to be included with the PE , its tricky to get the vlookup to look at the right amount of numbers from the rest of the postcode. e.g. PE21 is in danger of being mistaken for PE2 the way i was doing it. Also, you can have PE2 1JF which is in danger of being mistaken for PE21 if I dont use the space to select which numbers are part of the postal district. I am a bit more awake today so will have a look at Roger's solution. I also think i might be able to find another way around it by adding a couple of "IF(ISNA(..." formulas around what i am already using. not sure about that tho, will have to test it. Will let you know what solution I decide to use, thanks for your help everyone. "Toppers" wrote: Sorry , despite the long posting, it's not clear to me what you are doing/trying to? Are you trying to determine the postal district (and/or district) and then lookup the zone? why is this a problem? (I am UK-based so understand the post code format which includes several variants on the postal district). Perhaps a few examples would help (me certainly!). "oli merge" wrote: Hello all, Am a bit stuck here, probably partly because its a friday afternoon... Basically i am making a price calculator which works out how much it will cost to deliver to a postcode input by the user. There are 3 different price zones, and I have a list of which price zones go with which postal areas and districts. (NB: A UK postcode consists of the following format: CV22 2JF. the "CV" is the postal area, "cv21" is the postal district) The problem is that not all the postal areas are completely in one zone, so we have to look at the postal district - ie. instead of just looking for "PE" postal area it needs to look for PE1 or PE17 etc. Thats where the problem comes in. I have already spent ages working out how to make it find only the leading letters at the start by testing each letter consecutively to see if its a number with the code: =IF(ISNUMBER(VALUE(MID(D5,2,1))),LEFT(D5,1),IF(ISN UMBER(VALUE(MID(D5,3,1))),LEFT(D5,2),IF(ISNUMBER(V ALUE(MID(D5,3,1))),LEFT(D5,3),LEFT(D5,4)))) That works ok, another cell now looks at whether its an N/A error or not, and if it is tries including the some numbers as well: =IF(ISNA(IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))),IF(ISNUMBER(VALUE(MID(postcod e,4,1))),VLOOKUP(LEFT(postcode,4),'Zones data'!A1:C359,2,0),VLOOKUP(LEFT(postcode,3),'Zones data'!A1:C293,2,0)),IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))) Thing is, its not working on the postcodes which should be using the postal district because that is a split area. e.g. the postcode PE17 xyz should be identified as a zone two according to the table of zones data: Postcode start Zone ... CV 3 PE14 1 PE15 1 PE16 1 PE17 2 ...etc... But anything that requires numbers as part of the search is turning up a N/A error and I am too tired to follow my own formula now and work out where i am going wrong. Please help with possibly the longest question of all time! Thanks, Oli |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help with vlookup on uk postcodes
right, I cant seem to break it again. so now im after someone to spot where
the following technique might go wrong: I have a single cell for postcode input (named postcode, but also referred to as D5 just because Im not the tidiest of excel users). From that I work out the postal district in cell D13: =IF(ISNUMBER(VALUE(MID(D5,2,1))),LEFT(D5,1),IF(ISN UMBER(VALUE(MID(D5,3,1))),LEFT(D5,2),IF(ISNUMBER(V ALUE(MID(D5,3,1))),LEFT(D5,3),LEFT(D5,4)))) From that, I work out what zone (using a VLookup against another sheet where each postal area or postal district where applicable is on a line next to what zone) in cell D12 using this formula: =IF(ISNA(IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,2,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))),IF(ISNUMBER(VALUE(MID(postcod e,4,1))),VLOOKUP(LEFT(postcode,4),'Zones data'!A1:C359,2,0),VLOOKUP(LEFT(postcode,3),'Zones data'!A1:C293,2,0)),IF(ISNA(VLOOKUP(D13,'Zones data'!A1:C610,3,0)),VLOOKUP(LEFT(D13,2),'Zones data'!A1:C608,2,0),VLOOKUP(D13,'Zones data'!A1:C543,2,0))) Now, i think this is the cell where I trap for postcodes that are split between different zones with the ISNA. I still cant quite follow that convuluted formula though, so embarrisingly am not sure why it works and what I have to do to make it go wrong. im sure it wasnt working correctly on friday, but now it does seem to be. i need some genius to help here if possible. Once i have the zone, obviously its very easy to look that up on a pricing table so i wont go into that. Thanks then, if noone can see where an error might crop up I will close this thread since the problem is not reproduceable |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format of Postcodes | Excel Discussion (Misc queries) | |||
Format of postcodes | Excel Discussion (Misc queries) | |||
Postcodes starting with 0 | Excel Discussion (Misc queries) | |||
Standardising Postcodes | Excel Discussion (Misc queries) | |||
Postcodes | Excel Worksheet Functions |