Filter by specific data with multiple conditionss

[ExcelLearner0612]

I am using Excel 2010. I have a CSV file which I opened in Excel. It has a lot of data in it. I have to exclude specific data from it. For example: I have a bunch of zip codes, and I need to exclude the ones that end in 3, 5, or 7. I have tried the advanced filtering option to no avail. I have to display the zip codes that do NOT end with 3, 5, or 7. Please advise how would I go about this. I have watched numerous YouTube videos, but none seemed to apply to what I need. I have wasted 2 days trying to figure this out. I'm running out of time. Thank you in advance.

 

[Peter_SSs]

I would stick with Advanced Filter.

In my test I have the Zip codes in column D and I'm using Z1:Z2 as my Advanced Filter Criteria range

Z1: Leave blank
Z2: =ISNA(MATCH(RIGHT(D2,1)+0,{3,5,7},0))


Edit: BTW, welcome to the MrExcel board!

 

[ExcelLearner0612]

I would stick with Advanced Filter.

In my test I have the Zip codes in column D and I'm using Z1:Z2 as my Advanced Filter Criteria range

Z1: Leave blank
Z2: =ISNA(MATCH(RIGHT(D2,1)+0,{3,5,7},0))


Edit: BTW, welcome to the MrExcel board!

Thanks for your help. When I try this, it says reference is not valid. What is Z1 and Z2 stand for? The zip codes that end with either 3 or 5 or 7 need to be excluded.

 

[Peter_SSs]

What is Z1 and Z2 stand for?

Z1 and Z2 are cells on the worksheet - the top 2 cells in column Z.
You said that you had tried Advanced Filter so I assumed you knew what was meant by a Criteria Range since that is one of the input boxes in the Adv Filter dialog.

You said you had a sheet with "a lot of data". That is vague to me who cannot see your sheet, so I have no idea how many columns or rows that involves. Therefore I made up a small set of dummy data as shown below, just using columns A:F with random numbers in column D to represent Zip codes.

In column Z (it could be any available column) I left the top cell (Z1) blank.
In cell Z2 I put the formula that I posted above, and shown again below.

Excel 2010 32 bit
	A	B	C	D	E	F	Y	Z
1	Hdr 1	Hdr 2	Hdr 3	Zip	Hdr 5	Hdr 6
2	data	data	data	2973	data	data		FALSE
3	data	data	data	9225	data	data
4	data	data	data	299	data	data
5	data	data	data	94	data	data
6	data	data	data	4098	data	data
7	data	data	data	3392	data	data
8	data	data	data	1679	data	data
9	data	data	data	2690	data	data
10	data	data	data	2157	data	data
11	data	data	data	8113	data	data
12	data	data	data	2575	data	data
13	data	data	data	4046	data	data
14	data	data	data	3543	data	data
15	data	data	data	9774	data	data
16	data	data	data	2991	data	data
17	data	data	data	5208	data	data
18	data	data	data	5608	data	data
19	data	data	data	9845	data	data
20	data	data	data	7360	data	data
Exclude Zip Codes

Cell Formulas
Range	Formula
Z2	=ISNA(MATCH(RIGHT(D2,1)+0,{3,5,7},0))

I selected the data, A1:F20, went to the 'Data' ribbon tab and chose 'Advanced' in the Sort & Filter section.
In the dialog that opened I put the following information
Filter the list, in place
List Range: A1:F20
Criteria range: Z1:Z2
I clicked 'OK'

This is the result. Note the hidden row numbers at the left. Is it what you would want for that sample data?

Excel 2010 32 bit
	A	B	C	D	E	F
1	Hdr 1	Hdr 2	Hdr 3	Zip	Hdr 5	Hdr 6
4	data	data	data	299	data	data
5	data	data	data	94	data	data
6	data	data	data	4098	data	data
7	data	data	data	3392	data	data
8	data	data	data	1679	data	data
9	data	data	data	2690	data	data
13	data	data	data	4046	data	data
15	data	data	data	9774	data	data
16	data	data	data	2991	data	data
17	data	data	data	5208	data	data
18	data	data	data	5608	data	data
20	data	data	data	7360	data	data
Exclude Zip Codes

 

[ExcelLearner0612]

Thank you for your reply. Wow! This is so complicated. Yes, I saw the Criteria Range, but it was very confusing. So by selecting the entire worksheet in the List Range Field, then typing the formula in the cell of a blank column, and then choosing that as Criteria Range, will filter all the zip codes by excluding zip codes that end in 3, 5, 7? Will this filter apply to other columns in the worksheet since I have data in those columns that end with 3, or 5, or 7? My columns are form A through AJ.

 

[Peter_SSs]

So by selecting the entire worksheet in the List Range Field, ..

You would not select the entire worksheet for the List Range field, only where your data is A1:AJ??

... then typing the formula in the cell of a blank column, and then choosing that as Criteria Range, will filter all the zip codes by excluding zip codes that end in 3, 5, 7?

Clearly you cannot use column Z as your data goes beyond that. You would have to use column AK or something to the right of that.

My formula was =ISNA(MATCH(RIGHT(D2,1)+0,{3,5,7},0)) since my zip codes were in column D. You need to change that to whatever column your zip codes are in. That formula will only hide '3', '5' & '7' zip codes in that one column that the formula refers to.

If you have multiple columns with zip codes then you will need to give more details about which columns they are and how you would determine which rows to hide.
For example, if row 10 has a '3' zip code in column H but a '4' zip code in column AB, do you want that row hidden since it will hide a non-3,5,7 zip code as well as the '3' zip code because you can't hide part of a row.