Highlight Specific Postcodes From a List

[leeboyo1]

Hello

This should be easy but I can't seem to find how to do it.

What I want to do is create a worksheet template so that when a list of addresses are pasted into it, all the Scottish postcodes will be highlighted.


The postcode is already seperated from the address into its own column (K) to make things easier.
Next, I have a list of all the first parts of all Scottish postcodes e.g. G1 Glasgow AB14 Aberdeen, DD4 Dundee. I suppose this can be situated anywhere as a look-up reference Nb there are 430 different Scottish postcode areas in the list.

Therefore I need some kind of code to so that when I paste in a mixture of all UK postcodes (up to 1000 at any one time) it will highlight the Scottish ones based on the first part of the postcode from looking up my list.

I would prefer if the actual postcode cell box is highlighted, not the one adjacent as per below. This was just for illustration purposes.

For anyone unfarmiliar with UK postcodes, the XX could be any 2 alphabetical letters depending on the area.
But it's the first part before the space which is crucial and will dertimine if its Scottish or not.
This first part will always be in the format of one or two letters, followed by one or two numbers.

To illustrate:
[TABLE="width: 219"]
<tbody>[TR]
[TD]TN17 7XX
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
ME3 9XX
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
CF31 3XX
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
WN5 8XX
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
G77 2XX
[/TD]
[TD]Highlight
[/TD]
[/TR]
[TR]
[TD]
KA6 4XX
[/TD]
[TD]Highlight
[/TD]
[/TR]
[TR]
[TD]
EH3 9XX
[/TD]
[TD]Highlight
[/TD]
[/TR]
[TR]
[TD]
BA20 6XX
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
GL3 5XX
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
AB11 6XX
[/TD]
[TD]Highlight
[/TD]
[/TR]
[TR]
[TD]
M14 7XX
[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

What you're after is three things. First, we need to cull the text before the spaces in the UK Data and in the Scotland PC reference list. Then we need to determine whether or not each UK Data item is in the Scotland Postal code list; we can do that using the magic of array operations. And third we need to colour the UK Data to identify the Scottish ones, using Conditional Formatting of course.

For Conditional Formatting, highlight A2:A12 in my exemplar, execute CondFrmt on the ribbon, New Rule, Use a Formula..., paste this exactly in the Format values box =SUMPRODUCT(--(LEFT(A2,FIND(" ",A2)-1)=LEFT($G$2:$G$5,FIND(" ",$G$2:$G$5)-1)))>0 and then select a type of format (I chose yellow fill).

N.B. You can then delete columns H and C through E, which were there only for edification.

Presto.

Excel 2012

	A	B	C	D	E	F	G	H
UK Data	Text before space	In Scotland List?	Text before space
TN17 7XX	TN17	G77
ME3 9XX	ME3	KA6
CF31 3XX	CF31	EH3
WN5 8XX	WN5	AB11
G77 2XX	G77
KA6 4XX	KA6
EH3 9XX	EH3
BA20 6XX	BA20
GL3 5XX	GL3
AB11 6XX	AB11
M14 7XX	M14

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Scotland PC list[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"]G77 2XX[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"]KA6 4XX[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"]EH3 9XX[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"]AB11 6XX[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet26​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=LEFT(G2,FIND(" ",G2)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"]=LEFT(G3,FIND(" ",G3)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H4[/TH]
[TD="align: left"]=LEFT(G4,FIND(" ",G4)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H5[/TH]
[TD="align: left"]=LEFT(G5,FIND(" ",G5)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=LEFT(A2,FIND(" ",A2)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]=LEFT(A3,FIND(" ",A3)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=LEFT(A4,FIND(" ",A4)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]=LEFT(A5,FIND(" ",A5)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]=LEFT(A6,FIND(" ",A6)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C7[/TH]
[TD="align: left"]=LEFT(A7,FIND(" ",A7)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C8[/TH]
[TD="align: left"]=LEFT(A8,FIND(" ",A8)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"]=LEFT(A9,FIND(" ",A9)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C10[/TH]
[TD="align: left"]=LEFT(A10,FIND(" ",A10)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C11[/TH]
[TD="align: left"]=LEFT(A11,FIND(" ",A11)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C12[/TH]
[TD="align: left"]=LEFT(A12,FIND(" ",A12)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEFT(A2,FIND(" ",A2)-1)=LEFT($G$2:$G$5,FIND(" ",$G$2:$G$5)-1)))>0[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEFT(A3,FIND(" ",A3)-1)=LEFT($G$2:$G$5,FIND(" ",$G$2:$G$5)-1)))>0[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEFT(A4,FIND(" ",A4)-1)=LEFT($G$2:$G$5,FIND(" ",$G$2:$G$5)-1)))>0[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E5[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEFT(A5,FIND(" ",A5)-1)=LEFT($G$2:$G$5,FIND(" ",$G$2:$G$5)-1)))>0[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E6[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEFT(A6,FIND(" ",A6)-1)=LEFT($G$2:$G$5,FIND(" ",$G$2:$G$5)-1)))>0[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E7[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEFT(A7,FIND(" ",A7)-1)=LEFT($G$2:$G$5,FIND(" ",$G$2:$G$5)-1)))>0[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E8[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEFT(A8,FIND(" ",A8)-1)=LEFT($G$2:$G$5,FIND(" ",$G$2:$G$5)-1)))>0[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E9[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEFT(A9,FIND(" ",A9)-1)=LEFT($G$2:$G$5,FIND(" ",$G$2:$G$5)-1)))>0[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E10[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEFT(A10,FIND(" ",A10)-1)=LEFT($G$2:$G$5,FIND(" ",$G$2:$G$5)-1)))>0[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E11[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEFT(A11,FIND(" ",A11)-1)=LEFT($G$2:$G$5,FIND(" ",$G$2:$G$5)-1)))>0[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E12[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEFT(A12,FIND(" ",A12)-1)=LEFT($G$2:$G$5,FIND(" ",$G$2:$G$5)-1)))>0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[leeboyo1]

Hi

Thanks for your help with this. Unfortunately it doesn't work in practice.
I have a team who work huge lists of address data. Some of this work is manual and errors are made where there is a slightly different task for Scottish addresses. Some of the team can periodically miss the Scottish addresses (which is easily done). This is why I want Scottish Postcodes to be identified and highlighted so that errors will hopefully be eradicated.
The columns on the template cannot be added or removed as the address fields from another sheet are imported straight into it and then further columns next to each address are populated to complete the task based on the address. All lists and formulas etc need to be done in the background or on another tab, unfortunately the front page of the template cannot be disrupted.
Hope this makes sense?

Thanks in advance.

 

[JimM]

Try conditional formatting with this

ISTEXT(VLOOKUP(LEFT(A1,FIND(" ",A1)-1),$G$1:$G$7,1,FALSE))

define the format with whatever fill colour you like

copy the format down

Assumes your postcodes are in Column A , replace the $G$1:$G$7 with the range of your Scottish postcodes

 

[DRSteele]

I'm not following you. What I've done there works just fine. Please elaborate on where the proposed solution fails you.

You realise that you can design a template as above and move the formulas around to various worksheets if needed, right?

 

[leeboyo1]

Thanks for you help with this and taking the time.
Ok firstly I struggled because my postcode list is already cropped to the first 3 or 4 characters so therefore part of your formula is not required. However I added XXX to all of the postcodes in my list to get around that problem. I'll illustrate below (see fig1 and fig2.)
If you could simplify your formula to just look up the list rather than the left of it then I'd prefer that.

Secondly, I cannot get the formula to work if the scottish postcode list is on another worksheet withing the same document. For this to work, I need data on the front page which has been pasted in, so an extra column of scottish postcodes is not good no matter where on the sheet it is (even if hidden). Is there a solution to this?
I'm getting error message "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria".

The formula I was using isWhere colunm K is my postcode data list and column C on sheet1 is my scottish postcode list).
=SUMPRODUCT(--(LEFT(K2,FIND("" "",K2)-1)=LEFT(Sheet1!$C$1:$C$441,FIND("" "",Sheet1!$C$1:$C$441)-1)))>0

The one which I've got working for test purposes is: (Where colunm K is my postcode data list and column S is my scottish postcode list).
=SUMPRODUCT(--(LEFT(K2,FIND("" "",K2)-1)=LEFT($S$2:$S$442,FIND("" "",$S$2:$S$442)-1)))>0

Below is the list I'm using at the moment in fig1 to be able to use your formula, but I'd prefer to be using the list in fig2 which would require the formula to be altered.

Thanks

[TABLE="width: 388"]
<TBODY>[TR]
[TD]Scottish</SPAN>[/TD]
[TD]AB10 XXX</SPAN>[/TD]
[TD]Scottish</SPAN>[/TD]
[TD]AB10</SPAN>[/TD]
[/TR]
[TR]
[TD]Fig1</SPAN>[/TD]
[TD]AB11 XXX</SPAN>[/TD]
[TD]Fig2</SPAN>[/TD]
[TD]AB11</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AB12 XXX</SPAN>[/TD]
[TD][/TD]
[TD]AB12</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AB13 XXX</SPAN>[/TD]
[TD][/TD]
[TD]AB13</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AB14 XXX</SPAN>[/TD]
[TD][/TD]
[TD]AB14</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AB15 XXX</SPAN>[/TD]
[TD][/TD]
[TD]AB15</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AB16 XXX</SPAN>[/TD]
[TD][/TD]
[TD]AB16</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AB21 XXX</SPAN>[/TD]
[TD][/TD]
[TD]AB21</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AB22 XXX</SPAN>[/TD]
[TD][/TD]
[TD]AB22</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[DRSteele]

Conditional Formatting: I moved (i.e., I cut and pasted) my G2:G5 Scotland PC List to another sheet and it worked just fine; I got no error messages about not being able to use CondFrmt on another sheet. In fact, the formula in the CondFrmt dialogue box listed my other sheet.

Your query involved "But it's the first part before the space which is crucial," which necessitated a formula that looked for the letters to the left of the space. Now you need something different.

I'm trying to help but you're moving the goal-posts mid-game.