Formula or vba to assign a priceing band to UK postal code.

[A440]

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.

[BadlySpelledBuoy]

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

[BadlySpelledBuoy]

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

[A440]

Please see arttached

[A440]

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?

[BadlySpelledBuoy]

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?

That all depends on how they are banded. Are there set criteria to define which falls into which?

If not then one very long lookup table may be order of the day.

BSB

[A440]

Please see attached

[BadlySpelledBuoy]

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

[BadlySpelledBuoy]

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

[A440]

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.

[A440]

Thanks
I'll sort the codes in alphabetical order and mark out which is for A B or C.

[newdoverman]

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:

=VLOOKUP(LEFT(F3,4),$M$3:$N$10,2,0)

This is looking up the first 4 characters of the postcode in the table that is in M3:N10 as below:

	M	N
3	SY1	A
4	SY2	A
5	SY7	B
6	SY9	B
7	TF1	B
8	TF4	B
9	TF9	C
10	WV16	C

Here is your workbook back with the formula and the small table so that you can experiment with it.

[BadlySpelledBuoy]

Happy to help.

Thanks for the rep point.

BSB

[A440]

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.

[BadlySpelledBuoy]

By all means. Here to help.

BSB

[newdoverman]

Examine your references to be sure that they are actually pointing to the correct cells.