Design logic for matching UK Postal Codes

[Lucasvital]

Hi,

I have a list of persons in UK identified by a range of postal codes: 

Person	Postal Code Low	Postal Code High
Person A	BR0 0AA	BR9 9ZZ
Person B	DA0 0AA	DA9 9ZZ
Person Z	SW1A 0AA	SW1Z 9ZZ

I want match that specific Person to specific opportunities IDs based on Postal Code, but my other file has a specific Postal code that would follow in the range between Low and High

Opportunities	Zip Code
Opportunity Y	SW1E 5DH
Opportunity Z	EC1Y 0RR

Output would like:

Opportunities	Zip Code	Person
Opportunity Y	SW1E 5DH	Person Z
Opportunity Z	EC1Y 0RR	Person X

Anyone have any idea/suggestion on how to solve?

thanks so much

[BrandonB]

Is it always the 0 to 9 for the range? If so, I'm wondering if you could parse out the first three characters from the postal code and then just have

BR0

BR1

BR2

...

...

BR9

Then parse out the same in the opportunities and join on these characters. That way all that you would have to do is Generate Rows for each two digit postal code range so that they go 0 to 9

[Lucasvital]

Not really, there are cases that the first set of digits is 2 or 4 instead of 3, below is the whole list of potential postal codes:

Postal Code Low	Postal Code High
BR0 0AA	BR9 9ZZ
DA0 0AA	DA9 9ZZ
EC0A 0AA	EC9Z 9ZZ
E0 0AA	E9 9ZZ
IG0 0AA	IG9 9ZZ
RM0 0AA	RM9 9ZZ
SE0 0AA	SE9 9ZZ
SW0 0AA	SW9 9ZZ
WC0A 0AA	WC9Z 9ZZ
W0 0AA	W9 9ZZ
SM0 0AA	SM9 9ZZ
EN0 0AA	EN9 9ZZ
HA0 0AA	HA9 9ZZ
NW0 0AA	NW9 9ZZ
N0 0AA	N9 9ZZ
WD0 0AA	WD9 9ZZ
BS0 0AA	BS9 9ZZ
CR0 0AA	CR9 9ZZ
UB0 0AA	UB9 9ZZ
BS10 0AA	BS99 9ZZ
CR10 0AA	CR99 9ZZ
EN10 0AA	EN99 9ZZ
NW10 0AA	NW99 9ZZ
NW1W 0AA	NW1W 9ZZ
N10 0AA	N99 9ZZ
WD10 0AA	WD99 9ZZ
UB10 0AA	UB99 9ZZ
SM10 0AA	SM99 9ZZ
DA10 0AA	DA99 9ZZ
E10 0AA	E99 9ZZ
IG10 0AA	IG99 9ZZ
RM10 0AA	RM99 9ZZ
SE10 0AA	SE9Z 9ZZ
SE1P 0AA	SE1P 9ZZ
SW10 0AA	SW9Z 9ZZ
SW1A 0AA	SW1Z 9ZZ
W10 0AA	W99 9ZZ
N1C 0AA	N1C 9ZZ
N1P 0AA	N1P 9ZZ
AL0 0AA	AL9 9ZZ
B0 0AA	B9 9ZZ
CB0 0AA	CB9 9ZZ
CM0 0AA	CM9 9ZZ
CO0 0AA	CO9 9ZZ
CV0 0AA	CV9 9ZZ
DY0 0AA	DY9 9ZZ
GL0 0AA	GL9 9ZZ
HR0 0AA	HR9 9ZZ
IP0 0AA	IP9 9ZZ
LE0 0AA	LE9 9ZZ
LU0 0AA	LU9 9ZZ
MK0 0AA	MK9 9ZZ
NN0 0AA	NN9 9ZZ
NR0 0AA	NR9 9ZZ
PE0 0AA	PE9 9ZZ
SS0 0AA	SS9 9ZZ
SG0 0AA	SG9 9ZZ
TF0 0AA	TF9 9ZZ
WS0 0AA	WS9 9ZZ
WV0 0AA	WV9 9ZZ
WR0 0AA	WR9 9ZZ
DE0 0AA	DE9 9ZZ
LN0 0AA	LN9 9ZZ
NG0 0AA	NG9 9ZZ
ST0 0AA	ST9 9ZZ
LD0 0AA	LD9 9ZZ
LL0 0AA	LL9 9ZZ
NP0 0AA	NP9 9ZZ
SA0 0AA	SA9 9ZZ
SY0 0AA	SY9 9ZZ
B10 0AA	B99 9ZZ
CB10 0AA	CB99 9ZZ
CM10 0AA	CM99 9ZZ
CO10 0AA	CO99 9ZZ
CV10 0AA	CV99 9ZZ
DE10 0AA	DE99 9ZZ
DY10 0AA	DY99 9ZZ
GL10 0AA	GL99 9ZZ
IP10 0AA	IP99 9ZZ
LE10 0AA	LE99 9ZZ
LN10 0AA	LN99 9ZZ
LU10 0AA	LU99 9ZZ
MK10 0AA	MK99 9ZZ
NN10 0AA	NN99 9ZZ
NR10 0AA	NR99 9ZZ
NG10 0AA	NG99 9ZZ
PE10 0AA	PE99 9ZZ
SS10 0AA	SS99 9ZZ
AL10 0AA	AL99 9ZZ
SG10 0AA	SG99 9ZZ
ST10 0AA	ST99 9ZZ
TF10 0AA	TF99 9ZZ
WS10 0AA	WS99 9ZZ
WV10 0AA	WV99 9ZZ
WR10 0AA	WR99 9ZZ
LL10 0AA	LL99 9ZZ
NP10 0AA	NP99 9ZZ
SY10 0AA	SY99 9ZZ
SA10 0AA	SA99 9ZZ
BB0 0AA	BB9 9ZZ
BD0 0AA	BD9 9ZZ
BL0 0AA	BL9 9ZZ
CA0 0AA	CA9 9ZZ
CH0 0AA	CH9 9ZZ
CW0 0AA	CW9 9ZZ
FY0 0AA	FY9 9ZZ
DL0 0AA	DL9 9ZZ
DN0 0AA	DN9 9ZZ
DH0 0AA	DH9 9ZZ
HX0 0AA	HX9 9ZZ
HG0 0AA	HG9 9ZZ
HD0 0AA	HD9 9ZZ
IM0 0AA	IM9 9ZZ
HU0 0AA	HU9 9ZZ
TS0 0AA	TS9 9ZZ
LA0 0AA	LA9 9ZZ
LS0 0AA	LS9 9ZZ
L0 0AA	L9 9ZZ
M0 0AA	M9 9ZZ
NE0 0AA	NE9 9ZZ
OL0 0AA	OL9 9ZZ
PR0 0AA	PR9 9ZZ
S0 0AA	S9 9ZZ
SK0 0AA	SK9 9ZZ
SR0 0AA	SR9 9ZZ
WF0 0AA	WF9 9ZZ
WA0 0AA	WA9 9ZZ
WF0 0AA	WN9 9ZZ
YO0 0AA	YO9 9ZZ
BB10 0AA	BB99 9ZZ
BL10 0AA	BL99 9ZZ
BD10 0AA	BD99 9ZZ
CA10 0AA	CA99 9ZZ
CH10 0AA	CH99 9ZZ
CW10 0AA	CW99 9ZZ
DL10 0AA	DL99 9ZZ
DN10 0AA	DN99 9ZZ
DH10 0AA	DH99 9ZZ
HU10 0AA	HU99 9ZZ
IM10 0AA	IM99 9ZZ
LA10 0AA	LA99 9ZZ
LS10 0AA	LS99 9ZZ
L10 0AA	L99 9ZZ
M10 0AA	M99 9ZZ
NE10 0AA	NE99 9ZZ
OL10 0AA	OL99 9ZZ
PR10 0AA	PR99 9ZZ
S10 0AA	S99 9ZZ
SK10 0AA	SK99 9ZZ
TS10 0AA	TS99 9ZZ
WF10 0AA	WF99 9ZZ
WA10 0AA	WA99 9ZZ
YO10 0AA	YO99 9ZZ
HP0 0AA	HP9 9ZZ
OX0 0AA	OX9 9ZZ
BA0 0AA	BA9 9ZZ
BH0 0AA	BH9 9ZZ
BN0 0AA	BN9 9ZZ
CT0 0AA	CT9 9ZZ
CF0 0AA	CF9 9ZZ
DT0 0AA	DT9 9ZZ
EX0 0AA	EX9 9ZZ
GY0 0AA	GY9 9ZZ
GU0 0AA	GU9 9ZZ
JE0 0AA	JE9 9ZZ
PL0 0AA	PL9 9ZZ
PO0 0AA	PO9 9ZZ
RH0 0AA	RH9 9ZZ
ME0 0AA	ME9 9ZZ
SP0 0AA	SP9 9ZZ
SL0 0AA	SL9 9ZZ
SO0 0AA	SO9 9ZZ
SN0 0AA	SN9 9ZZ
TA0 0AA	TA9 9ZZ
TN0 0AA	TN9 9ZZ
TQ0 0AA	TQ9 9ZZ
TR0 0AA	TR9 9ZZ
GX0 0AA	GX9 9ZZ
RG0 0AA	RG9 9ZZ
KT0 0AA	KT9 9ZZ
TW0 0AA	TW9 9ZZ
BA10 0AA	BA99 9ZZ
HP10 0AA	HP99 9ZZ
OX10 0AA	OX99 9ZZ
BH10 0AA	BH99 9ZZ
CT10 0AA	CT99 9ZZ
CF10 0AA	CF99 9ZZ
DT10 0AA	DT99 9ZZ
EX10 0AA	EX99 9ZZ
GY10 0AA	GY99 9ZZ
GU10 0AA	GU99 9ZZ
KT10 0AA	KT99 9ZZ
PL10 0AA	PL99 9ZZ
PO10 0AA	PO99 9ZZ
RG10 0AA	RG99 9ZZ
RH10 0AA	RH99 9ZZ
ME10 0AA	ME99 9ZZ
SP10 0AA	SP99 9ZZ
SL10 0AA	SL99 9ZZ
SO10 0AA	SO99 9ZZ
SN10 0AA	SN99 9ZZ
TA10 0AA	TA99 9ZZ
TN10 0AA	TN99 9ZZ
TQ10 0AA	TQ99 9ZZ
TR10 0AA	TR99 9ZZ
TW10 0AA	TW99 9ZZ
GX10 0AA	GX99 9ZZ
BT0 0AA	BT9 9ZZ
BT10 0AA	BT99 9ZZ
AB0 0AA	AB9 9ZZ
DD0 0AA	DD9 9ZZ
DG0 0AA	DG9 9ZZ
EH0 0AA	EH9 9ZZ
FK0 0AA	FK9 9ZZ
G0 0AA	G9 9ZZ
IV0 0AA	IV9 9ZZ
KA0 0AA	KA9 9ZZ
KY0 0AA	KY9 9ZZ
ML0 0AA	ML9 9ZZ
PA0 0AA	PA9 9ZZ
PH0 0AA	PH9 9ZZ
TD0 0AA	TD9 9ZZ
ZE0 0AA	ZE3 9ZZ
KW0 0AA	KW9 9ZZ
HS0 0AA	HS9 9ZZ
AB10 0AA	AB99 9ZZ
DD10 0AA	DD99 9ZZ
DG10 0AA	DG99 9ZZ
EH10 0AA	EH99 9ZZ
FK10 0AA	FK99 9ZZ
G10 0AA	G99 9ZZ
HS10 0AA	HS99 9ZZ
IV10 0AA	IV99 9ZZ
KA10 0AA	KA99 9ZZ
KW10 0AA	KW99 9ZZ
KY10 0AA	KY99 9ZZ
ML10 0AA	ML99 9ZZ
PA10 0AA	PA99 9ZZ
PH10 0AA	PH99 9ZZ
TD10 0AA	TD99 9ZZ

[BrandonB]

This outlines the approach that I was looking to use. I don't work with UK Postal Codes very often so there are probably other criteria that are important. Maybe this is a start?

 

[BrandonB]

Generally converting a range to a list of lookup values uses generate rows in a similar fashion to my previous comment so I'm not sure if you just need the rules to be a bit more flexible based on the possible combinations. 

[JoeS]

Nice work @BrandonB  for having a go with UK postcodes from over the pond 😉

From looking at your problem, it looks like you can just use the Postcode area? AKA the first and/or second letter as the matching criteria?

Not sure if that's due to you posting a more simple scenario though?

To do that you can create the area in a new field using the RegEx tool:

Edit:

I have just seen you split out after 9 in the district, so not as straight forward as matching on postcode area. I'll reply back with a working solution....

[Lucasvital]

Hi @JoeS .

Wanted to follow up if you found another alternative and can share the working solution?

Thanks

[JoeS · Accepted solution]

Hi @Lucasvital 

Sorry I got distracted with our Inspire Conference.

Please find attached a workflow that builds out all the possible districts and keeps the relevant low and high.

You should then be able to match to the district column and can ignore the incode (last 3 characters).