advanced filter

L

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
Hi
I have a table like the one below. I created advanced filter criteria

criterial 1 - exam1 >50 [=B2>50]
criterial 2 - exam2 >70 [=C2>70]

and applied on this table

[TABLE="class: grid, width: 256"]
<tbody>[TR]
[TD="width: 64"]student[/TD]
[TD="width: 64"]exam1[/TD]
[TD="width: 64"]exam2[/TD]
[TD="width: 64"]result[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]70[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mary[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]77[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]alex[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sarah[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]90[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]david[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]90[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]anna[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Everything is working, but my question, is it possible somehow ask excel to put word pass, in the result column after doing the filtering? Like the following

[TABLE="class: outer_border, width: 256"]
<tbody>[TR]
[TD="width: 64"]student[/TD]
[TD="width: 64"]exam1[/TD]
[TD="width: 64"]exam2[/TD]
[TD="width: 64"]result[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]70[/TD]
[TD]pass[/TD]
[/TR]
[TR]
[TD]alex[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]40[/TD]
[TD]pass[/TD]
[/TR]
[TR]
[TD]david[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]90[/TD]
[TD]pass[/TD]
[/TR]
[TR]
[TD]anna[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD]pass[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 136"]
<tbody>[TR]
[TD="width: 136"]
Thank you[/TD]
[/TR]
</tbody>[/TABLE]

Ps: I just noticed that the advanced filter is giving me wrong answer! why anna is showing?
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
[TABLE="width: 284"]
<colgroup><col width="123" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4498;"> <col width="64" style="width: 48pt;" span="4"> <tbody>[TR]
[TD="width: 123, bgcolor: white"]student
[/TD]
[TD="width: 64, bgcolor: white"]exam1[/TD]
[TD="width: 64, bgcolor: white"]exam2[/TD]
[TD="width: 64, bgcolor: white"]CHECK[/TD]
[TD="width: 64, bgcolor: white"]result[/TD]
[/TR]
[TR]
[TD="width: 123, bgcolor: white"]john[/TD]
[TD="width: 64, bgcolor: white"]50[/TD]
[TD="width: 64, bgcolor: white"]70[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="width: 64, bgcolor: white"]PASS[/TD]
[/TR]
[TR]
[TD="width: 123, bgcolor: white"]mary[/TD]
[TD="width: 64, bgcolor: white"]30[/TD]
[TD="width: 64, bgcolor: white"]77[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="width: 64, bgcolor: white"]FAIL[/TD]
[/TR]
[TR]
[TD="width: 123, bgcolor: white"]alex[/TD]
[TD="width: 64, bgcolor: white"]70[/TD]
[TD="width: 64, bgcolor: white"]40[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="width: 64, bgcolor: white"]FAIL[/TD]
[/TR]
[TR]
[TD="width: 123, bgcolor: white"]sarah[/TD]
[TD="width: 64, bgcolor: white"]100[/TD]
[TD="width: 64, bgcolor: white"]90[/TD]
[TD="width: 64, bgcolor: white"]TRUE[/TD]
[TD="width: 64, bgcolor: white"]PASS[/TD]
[/TR]
[TR]
[TD="width: 123, bgcolor: white"]david[/TD]
[TD="width: 64, bgcolor: white"]11[/TD]
[TD="width: 64, bgcolor: white"]90[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="width: 64, bgcolor: white"]FAIL[/TD]
[/TR]
[TR]
[TD="width: 123, bgcolor: white"]anna[/TD]
[TD="width: 64, bgcolor: white"]20[/TD]
[TD="width: 64, bgcolor: white"]30[/TD]
[TD="width: 64, bgcolor: white"]FALSE[/TD]
[TD="width: 64, bgcolor: white"]FAIL
[/TD]
[/TR]
</tbody>[/TABLE]

Make a table like above. select it all then on data tab push filter

check column formula should be

only need one passing score for PASS =OR(B2>=50,C2>=70)
need to pass both =AND(B2>=50,C2>=70)

then result column formula =IF(D2=TRUE,"PASS","FAIL")

Then you only need filter using top of column filter.

Or add a button, if preferred and attach following vba code

in vba make code


Code: 
sub sort()
ActiveSheet.Range("$a$1:$e$10").AutoFilter Field:=5, Criteria1:="PASS"
end sub

does this accomplish what you wanted?
 
Upvote 0
[TABLE="width: 342"]
<tbody>[TR]
[TD="width: 97"] student
[/TD]
[TD="width: 91"] exam1
[/TD]
[TD="width: 91"] exam2
[/TD]
[TD="width: 93"] CHECK
[/TD]
[TD="width: 84"] result
[/TD]
[/TR]
[TR]
[TD="width: 97"] john
[/TD]
[TD="width: 91"]
50​
[/TD]
[TD="width: 91"]
70​
[/TD]
[TD="width: 93"] TRUE
[/TD]
[TD="width: 84"] PASS
[/TD]
[/TR]
[TR]
[TD="width: 97"] mary
[/TD]
[TD="width: 91"]
30​
[/TD]
[TD="width: 91"]
77​
[/TD]
[TD="width: 93"] FALSE
[/TD]
[TD="width: 84"] FAIL
[/TD]
[/TR]
[TR]
[TD="width: 97"] alex
[/TD]
[TD="width: 91"]
70​
[/TD]
[TD="width: 91"]
40​
[/TD]
[TD="width: 93"] FALSE
[/TD]
[TD="width: 84"] FAIL
[/TD]
[/TR]
[TR]
[TD="width: 97"] sarah
[/TD]
[TD="width: 91"]
100​
[/TD]
[TD="width: 91"]
90​
[/TD]
[TD="width: 93"] TRUE
[/TD]
[TD="width: 84"] PASS
[/TD]
[/TR]
[TR]
[TD="width: 97"] david
[/TD]
[TD="width: 91"]
11​
[/TD]
[TD="width: 91"]
90​
[/TD]
[TD="width: 93"] FALSE
[/TD]
[TD="width: 84"] FAIL
[/TD]
[/TR]
[TR]
[TD="width: 97"] anna
[/TD]
[TD="width: 91"]
20​
[/TD]
[TD="width: 91"]
30​
[/TD]
[TD="width: 93"] FALSE
[/TD]
[TD="width: 84"] FAIL
[/TD]
[/TR]
</tbody>[/TABLE]

Try making a table like above.
CHECK Column should have following formula
PASS BOTH TESTS FOR PASSING MARK =AND(B2>=50,C2>=70)
or
PASS ONE TEST FOR PASSING MARK =OR (B2>=50,C2>=70)


RESULT Column should have the following formula
=IF(D2=TRUE,"PASS","FAIL")
Then select ALL and click DATA and select Filter.
Hide CHECK Column
Now you can click filter at top to filter out any FAIL rows,or if you prefer, add a button on your
Worksheet and attach the following VBA code
Code: 
[FONT=Calibri][SIZE=3][COLOR=#000000]Sub HideFails()[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]ActiveSheet.Range("$A$1:$E$10").AutoFilter Field:=5,Criteria1:="PASS"[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
 
Upvote 0
lezawang said:
Ps: I just noticed that the advanced filter is giving me wrong answer! why anna is showing?
Click to expand...

Hi lezawang, can you explain your results table please. I can understand why you are querying Anna but if to get a pass means you need to meet the criteria in both exams I would expect Sarah to be the only one showing.
 
Upvote 0
Thank you all for your reply and help. The Advanced filter is not working for me. I created a new table

[TABLE="width: 190"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]exam1[/TD]
[TD]exam2[/TD]
[/TR]
[TR]
[TD]Linda[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD]Kim[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]87[/TD]
[/TR]
[TR]
[TD]Eric[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]Danny[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]20
[/TD]
[/TR]
</tbody>[/TABLE]


The criteria is B2>50 OR C2>50

So I put these criterial in 2 different cells E1 =B2>50 and E2 = C2>50 . I got the following filtered table, please notice that Sarah and Chris are not in the filtered table and I do not know why

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]name[/TD]
[TD="class: xl65, width: 64"]exam1[/TD]
[TD="class: xl65, width: 64"]exam2[/TD]
[/TR]
[TR]
[TD="class: xl66"]Mary[/TD]
[TD="class: xl66, align: right"]90[/TD]
[TD="class: xl66, align: right"]80[/TD]
[/TR]
[TR]
[TD="class: xl66"]John[/TD]
[TD="class: xl66, align: right"]59[/TD]
[TD="class: xl66, align: right"]66[/TD]
[/TR]
[TR]
[TD="class: xl66"]Kim[/TD]
[TD="class: xl66, align: right"]44[/TD]
[TD="class: xl66, align: right"]87[/TD]
[/TR]
[TR]
[TD="class: xl66"]Eric[/TD]
[TD="class: xl66, align: right"]45[/TD]
[TD="class: xl66, align: right"]77[/TD]
[/TR]
</tbody>[/TABLE]


The excel file can be downloaded from here:

https://drive.google.com/file/d/1DO4EDqgoSyqT3t8tml9G0sonH81n8Apq/view?usp=sharing
 
Upvote 0
See the link for criteria ranges

https://app.box.com/s/n6zvmwuc6pk8wfhjbg7xiim4tk4n0gzk

Excel Workbook
ABCDEFGHI
1nameexam1exam2
2criteria1 exam1>50*>50
3criteria2 exam2>50*>50
4This is the filtered table, why Chris and Sarah are not here?
5nameexam1exam2
6Linda3050 
7Alex10040PASSnameexam1exam2
8Adam1044Alex10040PASS
9Sarah8010PASSSarah8010PASS
10Mary9080PASSMary9080PASS
11John5966PASSJohn5966PASS
12Kim4487PASSKim4487PASS
13Eric4577PASSEric4577PASS
14Chris9841PASSChris9841PASS
15Danny3020
Sheet1


The formula I have added manually but no reason why you can't use a 4th criteria column.
 
Last edited:
Upvote 0
Thank you so much. I understand it now very well. Thank once again for your help, I really appreciate it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
IF and AND formulas for multiple criteria
Replies
6
Views
676
Peter_SSs
Peter_SSs
A
  • Solved
why the code is slow despite of using advanced filter with simple data
2 3
Replies
23
Views
431
abdelfattah
A
A
  • Solved
Application-defined or object defined Error when Filtering Listbox
Replies
4
Views
406
Peter_SSs
Peter_SSs
Lacan
  • Question Question
How to filtered/extract names and calculate difference between values?
2
Replies
16
Views
252
PeteWright
PeteWright
J
  • Solved
LET/VSTACK/FILTER - additional functionality question
2
Replies
14
Views
759
Jedi Master
J

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top