Hi, I have a sheet which contains customer names and addresses.
I would like a formula or vba to look at column L and assign a price band based on the post code.
Post codes are either 6 or 7 characters, 8 if the space is included as they are in my list. "AB1 2CD" or "AB12 3CD"
The price band would only need to consider the first part of the post code, "AB1". "AB1 2" would be nice though.
Thank you, your help is always appreciated.
You could use a lookup table, and either VLOOKUP or INDEX/MATCH to return a value based on a postcode text text string that included everything including one character after the space. That would give you the "AB1 2" functionality.
If you can post a sample workbook it's easier to show you how.
BSB
But to get you going and hopefully point you in the right direction, see attached for a VERY basic example of one way of doing this.
BSB
Please see arttached
Thank you for the reply.
I need to try and explain a bit better what I would like it to do.
I will have the 3 bands A, B and C. Would it be possible to have a range of post codes assigned to each band?
Same approach applied to your data.
Formula in the yellow cells. Lookup table is the green columns.
The lookup table could be tucked away in a hidden sheet for the sake of tidiness.
Hope that helps.
BSB
Last edited by BadlySpelledBuoy; 11-23-2015 at 01:46 PM. Reason: Correcting a typo
That all depends on how they are banded. Are there set criteria to define which falls into which?Originally Posted by A440
If not then one very long lookup table may be order of the day.
BSB
Thanks
I'll sort the codes in alphabetical order and mark out which is for A B or C.
Please see attached
If you take the postcodes and use Data Text to Columns and use Fixed instead of a delimiter and place the separator close to the second element of the postcode, you will end up with a 4 character code. The last character on a lot of the codes will be a space.
Use the LEFT function to extract the first 4 characters from the postcodes given in the addresses as the lookup key for VLOOKUP.
Enter this in G3 and fill down:
Formula:
Please Login or Register to view this content.
This is looking up the first 4 characters of the postcode in the table that is in M3:N10 as below:
SY1 A SY2 A SY7 B SY9 B TF1 B TF4 B TF9 C WV16 C
Here is your workbook back with the formula and the small table so that you can experiment with it.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
You could use the approach I showed above of using the postcode prefix and the first character of the suffix, however "SY22 6" will cause you problems as it appears under both Band A and Band B.
As you have this list of postcodes, why not simply put them in one long list with their respective bands and use that as a lookup table on the entire postcode (prefix and suffix)?
BSB
Ha ha, I was just working on doing that as your notification came through. it seems to work, I have also downloaded full lists of local area postcodes to cover new customers.
Thank you all for your help, I am happy with the solution I now have.
Happy to help.
Thanks for the rep point.
BSB
Your welcome.
I appear to have a problem with it now, it was working fine but it has now decided to return #REF! for all of them.
I must of moved something in the formula.
I'll come back to you if thats ok if I cant sort it.
By all means. Here to help.
BSB
Examine your references to be sure that they are actually pointing to the correct cells.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks