Comparing zip codes to keep in a column

[xtremekyter]

I have an excel spreadsheet with full addresses and also have a spreadsheet of zip codes to keep. I am trying to find an automated way to compare and highlight zip codes not on my list of keepers without having to do it manually as I have ove 11,000 records. I'm thinking it would be an "is not equal to" function but have never used that.Thanks in advance. Jeff

 

[JamesCanale]

You'll have to store leading zero zip codes as text '01112

Assumes zip codes are at the end and may be extended nine-digit zips. This could be very simplified if you assume it's always a 5-digit zip at the end of the address

MrExcelPlayground6.xlsx
	A	B	C
1	Address		Good ZIPs
2	123 Silly St 11111		12345-1234
3	456 first avenue 12345-1234		10036
4	One Penn Plaza 10036		90125
5	100-10 fifth avenue 11133		91111
6	Beverly Hills Yes 90125		01111
7	11 Jersey Street 01111
8	8 other street 51111
9
10
11
12
Sheet19

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A12	Expression	=NOT(OR(NOT(ISERR(SEARCH($C$2:$C$6,TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",25)),15)))))))	text	NO

 

[xtremekyter]

You'll have to store leading zero zip codes as text '01112

Assumes zip codes are at the end and may be extended nine-digit zips. This could be very simplified if you assume it's always a 5-digit zip at the end of the address

MrExcelPlayground6.xlsx
	A	B	C
1	Address		Good ZIPs
2	123 Silly St 11111		12345-1234
3	456 first avenue 12345-1234		10036
4	One Penn Plaza 10036		90125
5	100-10 fifth avenue 11133		91111
6	Beverly Hills Yes 90125		01111
7	11 Jersey Street 01111
8	8 other street 51111
9
10
11
12
Sheet19

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A12	Expression	=NOT(OR(NOT(ISERR(SEARCH($C$2:$C$6,TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",25)),15)))))))	text	NO

Thank you for your reply. I may have misled you by saying full address. The address is Name / Address / City / State / Zip so the zip code of the original is in a column by itself.

 

[JamesCanale]

Still with the leading zero zips stored as text.

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G
1	Name	Address	City	State	Zip		Good ZIPs
2	Jamie	123 aaa	New York	NY	10001		12345-1234
3	Fred	234 vvv	CityA	AB	12345-1234		10036
4	Harry	5466 fadsfa	CityB	CD	10111		90125
5	John	4 first st	CityC	EF	11133		91111
6	Mary	10-12 121 st	CityD	FG	90125		01111
7	Sally	94 fasdjkl	City	HI	01111
8	Kevin	10 askdfj	Place	JK	51111
Sheet19

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E12	Expression	=NOT(OR(NOT(ISERR(SEARCH($G$2:$G$6,E2)))))	text	NO

 

[xtremekyter]

First	Last	Address	City	St	Zip	Compare F to H	Keep these zips in F and highlight zips to delete
Gavin E.	Billingsley	2279 Keaton Chase Dr	Fleming Island	FL	32003​		32003​
Mason K.	Bily	596 Chivas Ct	Orange Park	FL	32073​		32004​
Sarah M.	Bimson	2344 Country Side Dr	Fleming Island	FL	32003​		32006​
Erica M.	Bingham	1198 Lenda Ln	Middleburg	FL	32068​		32007​
Keith L.	Bingham	1727 Poplar Dr	Orange Park	FL	32073​		32030​
Elaina M.	Birge	2625 Kermit Ct	Orange Park	FL	32065​		32033​
Brock E.	Bishop	3322 Southern Oaks Dr	Green Cove Springs	FL	32043​		32043​
John A. IV	Bishop	800 Hickory Knolls Dr	Green Cove Springs	FL	32043​		32044​
Sophie S.	Bishop	1361 Riviera Dr	Green Cove Springs	FL	32043​		32050​
Jianna V.	Bitterling	2076 Belle Grove Trce	FLEMING ISLAND	FL	32003​		32058​
Chase A.	Black	3924 Great Falls Loop	Middleburg	FL	32068​		32065​
Bryce G.	Blair	3950 Royal Pines Dr	Orange Park	FL	32065​		32067​
Kenneth L. III	Blair	1985 Sunshine Ct	Middleburg	FL	32068​		32068​
McKenzie A.	Blauch	533 Glendale Ln	Orange Park	FL	32065​		32073​
Ashley A.	Blazek	3064 Paddle Creek Dr	Green Cove Springs	FL	32044​
Garrett D.	Bleau	920 Fleming St	Fleming Island	FL	32003​
Kohl L.	Blevins	1192 Orchard Oriole Pl	Middleburg	FL	32068​
Amilah M.	Bobias	333 Island View Cir	Orange Park	FL	32073​
Jesse G.	Boesch	612 Charter Oaks Blvd	Orange Park	FL	32065​
Alyssa L.	Bohannon	109 Hercules Dr E	Orange Park	FL	32073​
Aralyn B.	Bolejack	8070 Blazing Star Rd	Melrose	FL	32666​
Gavin M.	Bolsega	1744 Eagle Watch Dr	Fleming Island	FL	32003​
Anne E.	Bolt	1806 West Lake Ct	Fleming Island	FL	32003​
Tabria C.	Booker	825 Stallion Way	Orange Park	FL	32065​
Kishana L.	Boone	2040 Wells Rd #8H	Orange Park	FL	32073​
Brendan M.	Booth	714 Eagle Cove Dr	Fleming Island	FL	32003​
Cael W.	Booth	1463 Brangus Rd	Middleburg	FL	32068​

 

[JamesCanale]

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G	H
1	First	Last	Address	City	St	Zip	Compare F to H	Keep these zips in F and highlight zips to delete
2	Gavin E.	Billingsley	2279 Keaton Chase Dr	Fleming Island	FL	32003		32003
3	Mason K.	Bily	596 Chivas Ct	Orange Park	FL	32073		32004
4	Sarah M.	Bimson	2344 Country Side Dr	Fleming Island	FL	32003		32006
5	Erica M.	Bingham	1198 Lenda Ln	Middleburg	FL	32068		32007
6	Keith L.	Bingham	1727 Poplar Dr	Orange Park	FL	32073		32030
7	Elaina M.	Birge	2625 Kermit Ct	Orange Park	FL	32065		32033
8	Brock E.	Bishop	3322 Southern Oaks Dr	Green Cove Springs	FL	32043		32043
9	John A. IV	Bishop	800 Hickory Knolls Dr	Green Cove Springs	FL	32043		32044
10	Sophie S.	Bishop	1361 Riviera Dr	Green Cove Springs	FL	32043		32050
11	Jianna V.	Bitterling	2076 Belle Grove Trce	FLEMING ISLAND	FL	32003		32058
12	Chase A.	Black	3924 Great Falls Loop	Middleburg	FL	32068		32065
13	Bryce G.	Blair	3950 Royal Pines Dr	Orange Park	FL	32065		32067
14	Kenneth L. III	Blair	1985 Sunshine Ct	Middleburg	FL	32068		32068
15	McKenzie A.	Blauch	533 Glendale Ln	Orange Park	FL	32065		32073
16	Ashley A.	Blazek	3064 Paddle Creek Dr	Green Cove Springs	FL	32044
17	Garrett D.	Bleau	920 Fleming St	Fleming Island	FL	32003
18	Kohl L.	Blevins	1192 Orchard Oriole Pl	Middleburg	FL	32068
19	Amilah M.	Bobias	333 Island View Cir	Orange Park	FL	32073
20	Jesse G.	Boesch	612 Charter Oaks Blvd	Orange Park	FL	32065
21	Alyssa L.	Bohannon	109 Hercules Dr E	Orange Park	FL	32073
22	Aralyn B.	Bolejack	8070 Blazing Star Rd	Melrose	FL	32666
23	Gavin M.	Bolsega	1744 Eagle Watch Dr	Fleming Island	FL	32003
24	Anne E.	Bolt	1806 West Lake Ct	Fleming Island	FL	32003
25	Tabria C.	Booker	825 Stallion Way	Orange Park	FL	32065
26	Kishana L.	Boone	2040 Wells Rd #8H	Orange Park	FL	32073
27	Brendan M.	Booth	714 Eagle Cove Dr	Fleming Island	FL	32003
28	Cael W.	Booth	1463 Brangus Rd	Middleburg	FL	32068
Sheet19

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F28	Expression	=NOT(OR(NOT(ISERR(SEARCH($H$2:$H$15,F2)))))	text	NO

 

[jtakw]

Hi,

Another way:

Book3.xlsx
	A	B	C	D	E	F	G	H
1	First	Last	Address	City	St	Zip	Compare F to H	Keep these zips in F and highlight zips to delete
2	Gavin E.	Billingsley	2279 Keaton Chase Dr	Fleming Island	FL	32003		32003
3	Mason K.	Bily	596 Chivas Ct	Orange Park	FL	32073		32004
4	Sarah M.	Bimson	2344 Country Side Dr	Fleming Island	FL	32003		32006
5	Erica M.	Bingham	1198 Lenda Ln	Middleburg	FL	32068		32007
6	Keith L.	Bingham	1727 Poplar Dr	Orange Park	FL	32073		32030
7	Elaina M.	Birge	2625 Kermit Ct	Orange Park	FL	32065		32033
8	Brock E.	Bishop	3322 Southern Oaks Dr	Green Cove Springs	FL	32043		32043
9	John A. IV	Bishop	800 Hickory Knolls Dr	Green Cove Springs	FL	32043		32044
10	Sophie S.	Bishop	1361 Riviera Dr	Green Cove Springs	FL	32043		32050
11	Jianna V.	Bitterling	2076 Belle Grove Trce	FLEMING ISLAND	FL	32003		32058
12	Chase A.	Black	3924 Great Falls Loop	Middleburg	FL	32068		32065
13	Bryce G.	Blair	3950 Royal Pines Dr	Orange Park	FL	32065		32067
14	Kenneth L. III	Blair	1985 Sunshine Ct	Middleburg	FL	32068		32068
15	McKenzie A.	Blauch	533 Glendale Ln	Orange Park	FL	32065		32073
16	Ashley A.	Blazek	3064 Paddle Creek Dr	Green Cove Springs	FL	32044
17	Garrett D.	Bleau	920 Fleming St	Fleming Island	FL	32003
18	Kohl L.	Blevins	1192 Orchard Oriole Pl	Middleburg	FL	32068
19	Amilah M.	Bobias	333 Island View Cir	Orange Park	FL	32073
20	Jesse G.	Boesch	612 Charter Oaks Blvd	Orange Park	FL	32065
21	Alyssa L.	Bohannon	109 Hercules Dr E	Orange Park	FL	32073
22	Aralyn B.	Bolejack	8070 Blazing Star Rd	Melrose	FL	32666
23	Gavin M.	Bolsega	1744 Eagle Watch Dr	Fleming Island	FL	32003
24	Anne E.	Bolt	1806 West Lake Ct	Fleming Island	FL	32003
25	Tabria C.	Booker	825 Stallion Way	Orange Park	FL	32065
26	Kishana L.	Boone	2040 Wells Rd #8H	Orange Park	FL	32073
27	Brendan M.	Booth	714 Eagle Cove Dr	Fleming Island	FL	32003
28	Cael W.	Booth	1463 Brangus Rd	Middleburg	FL	32068
Sheet939

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F28	Expression	=COUNTIF(H$2:H$15,F2)=0	text	NO

 

[xtremekyter]

Hi,

Another way:

Book3.xlsx
	A	B	C	D	E	F	G	H
1	First	Last	Address	City	St	Zip	Compare F to H	Keep these zips in F and highlight zips to delete
2	Gavin E.	Billingsley	2279 Keaton Chase Dr	Fleming Island	FL	32003		32003
3	Mason K.	Bily	596 Chivas Ct	Orange Park	FL	32073		32004
4	Sarah M.	Bimson	2344 Country Side Dr	Fleming Island	FL	32003		32006
5	Erica M.	Bingham	1198 Lenda Ln	Middleburg	FL	32068		32007
6	Keith L.	Bingham	1727 Poplar Dr	Orange Park	FL	32073		32030
7	Elaina M.	Birge	2625 Kermit Ct	Orange Park	FL	32065		32033
8	Brock E.	Bishop	3322 Southern Oaks Dr	Green Cove Springs	FL	32043		32043
9	John A. IV	Bishop	800 Hickory Knolls Dr	Green Cove Springs	FL	32043		32044
10	Sophie S.	Bishop	1361 Riviera Dr	Green Cove Springs	FL	32043		32050
11	Jianna V.	Bitterling	2076 Belle Grove Trce	FLEMING ISLAND	FL	32003		32058
12	Chase A.	Black	3924 Great Falls Loop	Middleburg	FL	32068		32065
13	Bryce G.	Blair	3950 Royal Pines Dr	Orange Park	FL	32065		32067
14	Kenneth L. III	Blair	1985 Sunshine Ct	Middleburg	FL	32068		32068
15	McKenzie A.	Blauch	533 Glendale Ln	Orange Park	FL	32065		32073
16	Ashley A.	Blazek	3064 Paddle Creek Dr	Green Cove Springs	FL	32044
17	Garrett D.	Bleau	920 Fleming St	Fleming Island	FL	32003
18	Kohl L.	Blevins	1192 Orchard Oriole Pl	Middleburg	FL	32068
19	Amilah M.	Bobias	333 Island View Cir	Orange Park	FL	32073
20	Jesse G.	Boesch	612 Charter Oaks Blvd	Orange Park	FL	32065
21	Alyssa L.	Bohannon	109 Hercules Dr E	Orange Park	FL	32073
22	Aralyn B.	Bolejack	8070 Blazing Star Rd	Melrose	FL	32666
23	Gavin M.	Bolsega	1744 Eagle Watch Dr	Fleming Island	FL	32003
24	Anne E.	Bolt	1806 West Lake Ct	Fleming Island	FL	32003
25	Tabria C.	Booker	825 Stallion Way	Orange Park	FL	32065
26	Kishana L.	Boone	2040 Wells Rd #8H	Orange Park	FL	32073
27	Brendan M.	Booth	714 Eagle Cove Dr	Fleming Island	FL	32003
28	Cael W.	Booth	1463 Brangus Rd	Middleburg	FL	32068
Sheet939

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F28	Expression	=COUNTIF(H$2:H$15,F2)=0	text	NO

Thank you but I am not too familiar with the conditional formatting tool. Where would I put that expression? Thanks again.

 

[xtremekyter]

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G	H
1	First	Last	Address	City	St	Zip	Compare F to H	Keep these zips in F and highlight zips to delete
2	Gavin E.	Billingsley	2279 Keaton Chase Dr	Fleming Island	FL	32003		32003
3	Mason K.	Bily	596 Chivas Ct	Orange Park	FL	32073		32004
4	Sarah M.	Bimson	2344 Country Side Dr	Fleming Island	FL	32003		32006
5	Erica M.	Bingham	1198 Lenda Ln	Middleburg	FL	32068		32007
6	Keith L.	Bingham	1727 Poplar Dr	Orange Park	FL	32073		32030
7	Elaina M.	Birge	2625 Kermit Ct	Orange Park	FL	32065		32033
8	Brock E.	Bishop	3322 Southern Oaks Dr	Green Cove Springs	FL	32043		32043
9	John A. IV	Bishop	800 Hickory Knolls Dr	Green Cove Springs	FL	32043		32044
10	Sophie S.	Bishop	1361 Riviera Dr	Green Cove Springs	FL	32043		32050
11	Jianna V.	Bitterling	2076 Belle Grove Trce	FLEMING ISLAND	FL	32003		32058
12	Chase A.	Black	3924 Great Falls Loop	Middleburg	FL	32068		32065
13	Bryce G.	Blair	3950 Royal Pines Dr	Orange Park	FL	32065		32067
14	Kenneth L. III	Blair	1985 Sunshine Ct	Middleburg	FL	32068		32068
15	McKenzie A.	Blauch	533 Glendale Ln	Orange Park	FL	32065		32073
16	Ashley A.	Blazek	3064 Paddle Creek Dr	Green Cove Springs	FL	32044
17	Garrett D.	Bleau	920 Fleming St	Fleming Island	FL	32003
18	Kohl L.	Blevins	1192 Orchard Oriole Pl	Middleburg	FL	32068
19	Amilah M.	Bobias	333 Island View Cir	Orange Park	FL	32073
20	Jesse G.	Boesch	612 Charter Oaks Blvd	Orange Park	FL	32065
21	Alyssa L.	Bohannon	109 Hercules Dr E	Orange Park	FL	32073
22	Aralyn B.	Bolejack	8070 Blazing Star Rd	Melrose	FL	32666
23	Gavin M.	Bolsega	1744 Eagle Watch Dr	Fleming Island	FL	32003
24	Anne E.	Bolt	1806 West Lake Ct	Fleming Island	FL	32003
25	Tabria C.	Booker	825 Stallion Way	Orange Park	FL	32065
26	Kishana L.	Boone	2040 Wells Rd #8H	Orange Park	FL	32073
27	Brendan M.	Booth	714 Eagle Cove Dr	Fleming Island	FL	32003
28	Cael W.	Booth	1463 Brangus Rd	Middleburg	FL	32068
Sheet19

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F28	Expression	=NOT(OR(NOT(ISERR(SEARCH($H$2:$H$15,F2)))))	text	NO

Thank you but I am not too familiar with the conditional formatting tool. Where would I put that expression? Thanks again.

 

[jtakw]

Thank you but I am not too familiar with the conditional formatting tool. Where would I put that expression? Thanks again.

Select Column F or the Column F range you need>
From Excel "Home" tab, Click "Conditional Formatting">
"New Rule">"Use a formula to determine which cells to format">
Enter my formula in Formula box>Click "Format">
Select "Fill", select fill color of your choice, click OK