Excel help please

[Gavgolf]

Hi all

Please can i request some assistance from any of the resident Excel gurus on here

Basically i want to take a list of names and addresses & postcodes and then allocate the list into 13 x regions sorted by postcode

Can anyway tell me how please

Thanks
G

[Mr Pointy]

Are there just 13 postcodes or does each of the 13 regions have a number of associated postcodes?

Does the postcode exist in a separate cell or is the whole address just one line of text ina cell?

[Gavgolf]

Postcode is all in one cell and could be any U.K. postcode
Each region will basically be a combination of multiple postcodes ie NG, DE, LE

[tertius]

Not sure I fully follow the ask but if I understand correctly I would approach it something like this:

1. I am assuming that only the first two letters (e.g. DE from your example) are important: so create a reference list that has a column of the possible two letter codes and against it (in a second column) the relevant region identifier, so again from our example, LE, DE and NG would all be region 10 (for the sake of argument)

2. Create a new column alongside the source addresses to hold the first two letters of the postcode, use LEFT to trim off the first two letters, e.g. "=LEFT(K2,2)" where the full postcode is in the cell K2

3. then in the cell where I want the region value to go use a VLookup to check for that 2 letter value in the reference table (table is in the cells D2 to E7 in my example, I only created a short table, yours will be longer, the $ ensures it always checks exactly the cells for the table)

=VLOOKUP(L2,$D$2:$E$6, 2)

4. copy the formulas all the way down the addresses

[BillyCasper]

13 (official) UK regions sounds right. There are circa 125 two letter post code prefixes. There is a look up table that groups the 125 post code prefixes by region which might help with VLOOKUP.

UK region Postcode prefix Postcode district
Channel Islands GY Guernsey
Channel Islands JE Jersey
East England PE Peterborough
East Midlands DE Derby
East Midlands DN Doncaster
East Midlands LE Leicester
East Midlands LN Lincoln
East Midlands NG Nottingham
East Midlands S Sheffield
East of England AL St. Albans
East of England CB Cambridge
East of England CM Chelmsford
East of England CO Colchester
East of England HP Hemel
East of England IP Ipswich
East of England LU Luton
East of England NR Norwich
East of England SG Stevenage
East of England SS Southend
Greater London BR Bromley
Greater London CR Croydon
Greater London DA Dartford
Greater London E London
Greater London EC London
Greater London EN Enfield
Greater London HA Harrow
Greater London IG Ilford
Greater London KT Kingston
Greater London N London
Greater London NW London
Greater London RM Romford
Greater London SE London
Greater London SM Sutton
Greater London SW London
Greater London TW Twickenham
Greater London UB Southall
Greater London W London
Greater London WC London
Greater London WD Watford
Isle of Man IM Isle of Man
North East DH Durham
North East DL Darlington
North East HG Harrogate
North East HU Hull
North East LS Leeds
North East NE Newcastle
North East SR Sunderland
North East TS Cleveland
North East WF Wakefield
North East YO York
North West BB Blackburn
North West BD Bradford
North West BL Bolton
North West CA Carlisle
North West CH Chester
North West CW Crewe
North West FY Blackpool
North West HD Huddersfield
North West HX Halifax
North West L Liverpool
North West LA Lancaster
North West M Manchester
North West OL Oldham
North West PR Preston
North West SK Stockport
North West WA Warrington
North West WN Wigan
Northern Ireland BT Belfast
Scotland AB Aberdeen
Scotland DD Dundee
Scotland DG Dumfries
Scotland EH Edinburgh
Scotland FK Falkirk
Scotland G Glasgow
Scotland HS Comhairle nan Eilean Siar
Scotland IV Inverness
Scotland KA Kilmarnock
Scotland KW Kirkwall
Scotland KY Kirkaldy
Scotland ML Motherwell
Scotland PA Paisley
Scotland PH Perth
Scotland TD Galashiels
Scotland ZE Shetland
South East BN Brighton
South East CT Canterbury
South East GU Guilford
South East ME Medway
South East MK Milton Keynes
South East OX Oxford
South East PO Portsmouth
South East RG Reading
South East RH Redhill
South East SL Slough
South East SO Southampton
South East TN Tonbridge
South West BA Bath
South West BH Bournemouth
South West BS Bristol
South West DT Dorchester
South West EX Exeter
South West GL Gloucester
South West PL Plymouth
South West SN Swindon
South West SP Salisbury
South West TA Taunton
South West TQ Torquay
South West TR Truro
Wales CF Cardiff
Wales LD Llandrindod
Wales LL Llandudno
Wales NP Newport
Wales SA Swansea
Wales SY Shrewsbury
West Midlands B Birmingham
West Midlands CV Coventry
West Midlands DY Dudley
West Midlands HR Hereford
West Midlands NN Northampton
West Midlands ST Stoke on Trent
West Midlands TF Telford
West Midlands WR Worcester
West Midlands WS Walsall
West Midlands WV Wolverhampton

[Mr Pointy]

Not sure I fully follow the ask but if I understand correctly I would approach it something like this:

1. I am assuming that only the first two letters (e.g. DE from your example) are important: so create a reference list that has a column of the possible two letter codes and against it (in a second column) the relevant region identifier, so again from our example, LE, DE and NG would all be region 10 (for the sake of argument)

2. Create a new column alongside the source addresses to hold the first two letters of the postcode, use LEFT to trim off the first two letters, e.g. "=LEFT(K2,2)" where the full postcode is in the cell K2

3. then in the cell where I want the region value to go use a VLookup to check for that 2 letter value in the reference table (table is in the cells D2 to E7 in my example, I only created a short table, yours will be longer, the $ ensures it always checks exactly the cells for the table)

=VLOOKUP(L2,$D$2:$E$6, 2)

4. copy the formulas all the way down the addresses

Step 2 will fail because not all postcodes have two letters at the start - see Sheffield & East London in the above list.

[tertius]

Step 2 will fail because not all postcodes have two letters at the start - see Sheffield & East London in the above list.

OK, didn't think that through - there's probably a clever coding answer, but an easy answer would be to just create entries in the lookup table for E1 to E9, etc.

[Gavgolf]

Thanks All - spot on and now working as required :)