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:

TN17 7XX
ME3 9XX
CF31 3XX
WN5 8XX
G77 2XX	Highlight
KA6 4XX	Highlight
EH3 9XX	Highlight
BA20 6XX
GL3 5XX
AB11 6XX	Highlight
M14 7XX

<tbody>
</tbody>

 

[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
1	UK Data		Text before space		In Scotland List?		Scotland PC list	Text before space
2	TN17 7XX		TN17		FALSE		G77 2XX	G77
3	ME3 9XX		ME3		FALSE		KA6 4XX	KA6
4	CF31 3XX		CF31		FALSE		EH3 9XX	EH3
5	WN5 8XX		WN5		FALSE		AB11 6XX	AB11
6	G77 2XX		G77		TRUE
7	KA6 4XX		KA6		TRUE
8	EH3 9XX		EH3		TRUE
9	BA20 6XX		BA20		FALSE
10	GL3 5XX		GL3		FALSE
11	AB11 6XX		AB11		TRUE
12	M14 7XX		M14		FALSE

<tbody>
</tbody>

Sheet26​

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

<tbody>
</tbody>

 

[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

Scottish</SPAN>	AB10 XXX</SPAN>	Scottish</SPAN>	AB10</SPAN>
Fig1</SPAN>	AB11 XXX</SPAN>	Fig2</SPAN>	AB11</SPAN>
	AB12 XXX</SPAN>		AB12</SPAN>
	AB13 XXX</SPAN>		AB13</SPAN>
	AB14 XXX</SPAN>		AB14</SPAN>
	AB15 XXX</SPAN>		AB15</SPAN>
	AB16 XXX</SPAN>		AB16</SPAN>
	AB21 XXX</SPAN>		AB21</SPAN>
	AB22 XXX</SPAN>		AB22</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>

 

[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.