advanced filter

C

Comfy

Well-known Member

Joined

Dec 21, 2009

Messages

3,386

• Aug 22, 2014

• #2

Use the Advanced Filter.

If my data looked like this:

Excel 2010

	A
8	Job
9	Bus Driver
10	Driver of Cars
11	Van Man
12	Florist

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

I would create another range as the filter criteria which would look something like this:
Excel 2010

	A
1	Job
2	*Bus*
3	*Car*
4	*Van*

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

This would then filter the data to show items that contain Bus or Car or Van.

 

Upvote 0

L

lemery

New Member

Joined

Jul 31, 2014

Messages

36

• Aug 22, 2014

• #3

It's not working. does their have to be a column
title?

 

Upvote 0

C

Comfy

Well-known Member

Joined

Dec 21, 2009

Messages

3,386

• Aug 22, 2014

• #4

Yes, you need to tell Excel which column to apply the filter too, even if you are only filtering one column.

 

Upvote 0

L

lemery

New Member

Joined

Jul 31, 2014

Messages

36

• Aug 22, 2014

• #5

I click in the first box and then highlight column E that has a title and 5000 job titles under that, then I go to the second box click in it and then highlight the column title and the words underneath it and click ok and it shows me zero results. i can see that there are job titles in column E that have the below keywords in the title.

contains

*nurse*

*recruit*

*np*

*physician assistant*

*coordinator*

*scheduler*

*administration*

*billing*

*arnp*

*staffing*

<tbody>
</tbody>

column E is on a different sheet than the keywords above

 

Last edited: Aug 22, 2014

Upvote 0

C

Comfy

Well-known Member

Joined

Dec 21, 2009

Messages

3,386

• Aug 22, 2014

• #6

The Title/Header for Column E must be the aame as the Title/header for your filter list

 

Upvote 0

L

lemery

New Member

Joined

Jul 31, 2014

Messages

36

• Aug 22, 2014

• #7

Is it searching for only this word in the asterisks or is it looking for the word in the asterisks anywhere in the job title?
nurse practitioner would show up in the results because I have nurse as a keyword.

At this point it either gives me back no results or doesn't change the results at all. Does it matter if I start with a filter already in place (little arrows by column title) before pressing advanced filter?

 

Upvote 0

C

Comfy

Well-known Member

Joined

Dec 21, 2009

Messages

3,386

• Aug 22, 2014

• #8

lemery said:

Is it searching for only this word in the asterisks or is it looking for the word in the asterisks anywhere in the job title?
nurse practitioner would show up in the results because I have nurse as a keyword.

Click to expand...

It match the string within the asterisks to any cell that contains the string

lemery said:

At this point it either gives me back no results or doesn't change the results at all. Does it matter if I start with a filter already in place (little arrows by column title) before pressing advanced filter?

Click to expand...

The advanced filter will override the auto filter.


Can you paste some of your data and your filter criteria here for me to look at?

 

Upvote 0

M

Marts81

New Member

Joined

Feb 19, 2014

Messages

10

• Aug 22, 2014

• #9

Would it be possible for you to write the 20 names in a column close to the E column, give the same title as the E column, don't put the * before the names and advance filter selecting "Filter the list in place"? Once the word you filtered remain in the E column, delete them and then remove the filter. You will have back the 5000 items less the filtered ones.

 

Upvote 0

L

lemery

New Member

Joined

Jul 31, 2014

Messages

36

• Aug 22, 2014

• #10

how do I make the search "Contains" instead of "exact"?

 

Upvote 0