How I use Advace Filter option for this?

[thara05]

I want to filter the data if 'position' equal to clerk or secretory using advance filter option.

This is my data table :

Name	Position	Salary	Net Salary
Even	Accountant	Rs.80,000.00	Rs.64,000.00
Reymond	Administrator	Rs.89,000.00	Rs.71,200.00
Willee	Administrator	Rs.75,000.00	Rs.60,000.00
Ben	Administrator	Rs.85,000.00	Rs.68,000.00
Deen	Clerk	Rs.60,000.00	Rs.48,000.00
Fillen	Clerk	Rs.40,000.00	Rs.32,000.00
Moor	Clerk	Rs.35,000.00	Rs.28,000.00
Sandy	Clerk	Rs.50,000.00	Rs.40,000.00
Fred	Clerk	Rs.59,000.00	Rs.47,200.00
Nancy	Clerk	Rs.55,000.00	Rs.44,000.00
Rondy	Manager	Rs.94,000.00	Rs.75,200.00
Noor	Manager	Rs.78,000.00	Rs.62,400.00
Dandy	Secretory	Rs.40,000.00	Rs.32,000.00
Grue	Secretory	Rs.56,000.00	Rs.44,800.00
Salle	Secretory	Rs.55,000.00	Rs.44,000.00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

I had tried it like this, but I it wasn't no luck.

Name	Position	Position	Salary	Net Salary
	Clerk	Secretory

<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

hope somebody may help me.

Thank you.

 

[oldbrewer]

Name	Position	Salary	Net Salary
Even	Accountant	80,000.00	64,000.00
Reymond	Administrator	89,000.00	71,200.00
Willee	Administrator	75,000.00	60,000.00
Ben	Administrator	85,000.00	68,000.00
Deen	Clerk	60,000.00	48,000.00
Fillen	Clerk	40,000.00	32,000.00
Moor	Clerk	35,000.00	28,000.00
Sandy	Clerk	50,000.00	40,000.00
Fred	Clerk	59,000.00	47,200.00
Nancy	Clerk	55,000.00	44,000.00
Rondy	Manager	94,000.00	75,200.00
Noor	Manager	78,000.00	62,400.00
Dandy	Secretory	40,000.00	32,000.00
Grue	Secretory	56,000.00	44,800.00
Salle	Secretory	55,000.00	44,000.00
						Position	(All)
						Name	Data	Total
						Dandy	Sum of Salary	40000
							Sum of Net Salary	32000
						Deen	Sum of Salary	60000
							Sum of Net Salary	48000
						Fillen	Sum of Salary	40000
							Sum of Net Salary	32000
						Fred	Sum of Salary	59000
							Sum of Net Salary	47200
						Grue	Sum of Salary	56000
							Sum of Net Salary	44800
						Moor	Sum of Salary	35000
							Sum of Net Salary	28000
						Nancy	Sum of Salary	55000
							Sum of Net Salary	44000
						Salle	Sum of Salary	55000
							Sum of Net Salary	44000
						Sandy	Sum of Salary	50000
							Sum of Net Salary	40000
						Total Sum of Salary	450000
						Total Sum of Net Salary	360000

<colgroup><col><col><col><col><col span="3"><col><col><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

a simple pivot table with the non required job titles excluded - you could pull the data to a neater formatted table if you wanted....

 

[thara05]

I need to filter data using advance filter option

 

[Aladin Akyurek]

We have in A:D with headers in row 1...

Name	Position	Salary	Net Salary
Even	Accountant	Rs.80,000.00	Rs.64,000.00
Reymond	Administrator	Rs.89,000.00	Rs.71,200.00
Willee	Administrator	Rs.75,000.00	Rs.60,000.00
Ben	Administrator	Rs.85,000.00	Rs.68,000.00
Deen	Clerk	Rs.60,000.00	Rs.48,000.00
Fillen	Clerk	Rs.40,000.00	Rs.32,000.00
Moor	Clerk	Rs.35,000.00	Rs.28,000.00
Sandy	Clerk	Rs.50,000.00	Rs.40,000.00
Fred	Clerk	Rs.59,000.00	Rs.47,200.00
Nancy	Clerk	Rs.55,000.00	Rs.44,000.00
Rondy	Manager	Rs.94,000.00	Rs.75,200.00
Noor	Manager	Rs.78,000.00	Rs.62,400.00
Dandy	Secretary	Rs.40,000.00	Rs.32,000.00
Grue	Secretary	Rs.56,000.00	Rs.44,800.00
Salle	Secretary	Rs.55,000.00	Rs.44,000.00

<TBODY>
</TBODY>

Leave F1 empty.
Enter the following in F2:

=OR(B2="clerk",B2="secretary")

Now follow the following steps...
Select F1:F2.
Fire up Advanced Filter
Click OK.
For List range: Select the range in A:D including the headers.
For Criteria range: Select again F1:F2.
For Copy to, select cell F3.
Click OK.

The criteria range along with the output will look like:

FALSE
Name	Position	Salary	Net Salary
Deen	Clerk	Rs.60,000.00	Rs.48,000.00
Fillen	Clerk	Rs.40,000.00	Rs.32,000.00
Moor	Clerk	Rs.35,000.00	Rs.28,000.00
Sandy	Clerk	Rs.50,000.00	Rs.40,000.00
Fred	Clerk	Rs.59,000.00	Rs.47,200.00
Nancy	Clerk	Rs.55,000.00	Rs.44,000.00
Dandy	Secretary	Rs.40,000.00	Rs.32,000.00
Grue	Secretary	Rs.56,000.00	Rs.44,800.00
Salle	Secretary	Rs.55,000.00	Rs.44,000.00

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3555" width=100><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4579" width=129><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4181" width=118><TBODY>
</TBODY>

 

[pgc01]

I need to filter data using advance filter option

Out of curiosity: What does this mean? What does it matter what's the method you use if it solves the problem?

About what you posted, you have "Clerk" and "Secretory" in the same row. This means "Clerk" AND "Secretory".

If you want "Clerk" OR "Secretory" they must be in different rows.

For ex. erase "Secretory" from the second row and write it in row 3 and then run the Advanced Filter. Don't forget to include the third row in the Criteria range.

 

[thara05]

@Aladin Akyurek, Thank you very much for your answer. Now its work for me.

 

[Aladin Akyurek]

@Aladin Akyurek, Thank you very much for your answer. Now its work for me.

You are welcome.