Advance Filter Macro

N

Negi1984

Board Regular
Joined
May 6, 2011
Messages
199
Hi All,

I have data in below format :-
Now I want to add a filter as below mentioned criteria :-

1) Picklisted : Should be New
2) 410(A) : Should remove latest 6 dates from filter
3) GR1 : Should be False
4) GR01 : Should be select all numbers & numbers where 2 numbers mentioned with text Rej.

I tried by myself with without macro with advance filter , but unable to found any solution, how to filter out latest 6 dates from filter. Please assist.
[TABLE="width: 378"]
<tbody>[TR]
[TD]State[/TD]
[TD] Program[/TD]
[TD]Picklisted[/TD]
[TD]410 (A)[/TD]
[TD]GRO1[/TD]
[TD]GR1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]A[/TD]
[TD]New[/TD]
[TD]7-Aug-13[/TD]
[TD]2334[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]NSW[/TD]
[TD]B[/TD]
[TD]Old site[/TD]
[TD]24-Jul-13[/TD]
[TD]2442[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]NSW[/TD]
[TD]C[/TD]
[TD]New[/TD]
[TD]21-Oct-15[/TD]
[TD]3444[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]NSW[/TD]
[TD]D[/TD]
[TD]New[/TD]
[TD]15-Jan-15[/TD]
[TD]2345[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]NSW[/TD]
[TD]E[/TD]
[TD]Old site[/TD]
[TD]3-Sep-15[/TD]
[TD]5223[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]G[/TD]
[TD]New[/TD]
[TD]11-Jun-14[/TD]
[TD]71,74 Rej[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]QLD[/TD]
[TD]D[/TD]
[TD]Old site[/TD]
[TD]29-Aug-13[/TD]
[TD]4555[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]QLD[/TD]
[TD]S[/TD]
[TD]New[/TD]
[TD]20-Sep-13[/TD]
[TD]31,74, Rej[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]QLD[/TD]
[TD]SS[/TD]
[TD]Old site[/TD]
[TD]15-Jul-13[/TD]
[TD]7470[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]QLD[/TD]
[TD]ER[/TD]
[TD]Old site[/TD]
[TD]16-Jul-13[/TD]
[TD]7470[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]WA[/TD]
[TD]4[/TD]
[TD]New[/TD]
[TD]17-Sep-13[/TD]
[TD]74,Rej[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]WA[/TD]
[TD]56[/TD]
[TD]Old site[/TD]
[TD]12-Aug-13[/TD]
[TD]7470[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Maybe using a formula as criteria, but i don't understand the last criteria
"4) GR01 : Should be select all numbers & numbers where 2 numbers mentioned with text Rej."

Could you clarify?

M.
 
Upvote 0
Hi Marcelo,

4) I mean to say that if any number like 1234 , Rej mentioned than I not needed that numbers in filter but if sequence is like 1234, 4546 , Rej or 1234 , 4567 Rej than I need those records in filter.

Could you please suggest , how to add all these criteria's in MAcro.
Regards,
Rajender
 
Upvote 0
Rajender,

Still not clear - at least for me. Don't understand what you mean by:
"...any number like 1234, Rej mentioned than I not needed that numbers"
"...but if sequence like... 1234, 4546, Rej or 1234, 4567 Rej..."

What you mean by "mentioned"? What "sequence" are you talking about?
All i can see is that column GRO1 contains numbers and some numbers followed by the text Rej.
I can't see any mentioned number or any sequence. :confused:

Maybe if you tell us the expected result (visible rows after filter) considering the data sample you've provided, explaining the logic to achieve such results, it would be easier to understand the last criteria.

M.
 
Last edited:
Upvote 0
Hi Marcelo,

Sorry for not able to clear my doubt.
Sequence means the value mentioned in a cell sequence.
Below are the steps which I need to follow for my desired result :-

1) Filter on Column Picklisted as New
2) Now remove latest 6 dates from the filter
3) now Select False in column GR
4) In GR column Select all the numbers & cells which contain data as 122,234, Rej or 245,45 Rej

Below also sample output and data link providing.

https://1drv.ms/x/s!Ap80Ku6M2Tw5gTLgnxh4wtz0emfS

Thanks in advance for your valuable support.
Regards,
Rajender
 
Upvote 0
I downloaded your file, but didn't understand how did you get this output.


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D
​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD="bgcolor: #FFFF00"]
OUTPUT​
[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
State​
[/TD]
[TD]
Program​
[/TD]
[TD]
Picklisted​
[/TD]
[TD]
410 (A)​
[/TD]
[TD]
GRO1​
[/TD]
[TD]
GR1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
NSW​
[/TD]
[TD]
B​
[/TD]
[TD]
New​
[/TD]
[TD]
02/jan/17​
[/TD]
[TD]
2442​
[/TD]
[TD]
FALSE
​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
NSW​
[/TD]
[TD]
D​
[/TD]
[TD]
New​
[/TD]
[TD]
04/jan/17​
[/TD]
[TD]
2345​
[/TD]
[TD]
FALSE
​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
0​
[/TD]
[TD]
G​
[/TD]
[TD]
New​
[/TD]
[TD]
02/jan/17​
[/TD]
[TD]
71,74 Rej​
[/TD]
[TD]
FALSE
​
[/TD]
[/TR]
</tbody>[/TABLE]


According to your conditions i was expecting this

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
OUTPUT​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
State​
[/TD]
[TD]
Program​
[/TD]
[TD]
Picklisted​
[/TD]
[TD]
410 (A)​
[/TD]
[TD]
GRO1​
[/TD]
[TD]
GR1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
NSW​
[/TD]
[TD]
B​
[/TD]
[TD]
New​
[/TD]
[TD]
02/jan/17​
[/TD]
[TD]
2442​
[/TD]
[TD]
FALSE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
NSW​
[/TD]
[TD]
D​
[/TD]
[TD]
New​
[/TD]
[TD]
04/jan/17​
[/TD]
[TD]
2345​
[/TD]
[TD]
FALSE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
NSW​
[/TD]
[TD]
E​
[/TD]
[TD]
New​
[/TD]
[TD]
05/jan/17​
[/TD]
[TD]
5223​
[/TD]
[TD]
FALSE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
0​
[/TD]
[TD]
G​
[/TD]
[TD]
New​
[/TD]
[TD]
02/jan/17​
[/TD]
[TD]
71,74 Rej​
[/TD]
[TD]
FALSE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
QLD​
[/TD]
[TD]
S​
[/TD]
[TD]
New​
[/TD]
[TD]
08/jan/17​
[/TD]
[TD]
31,74, Rej​
[/TD]
[TD]
FALSE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
WA​
[/TD]
[TD]
4​
[/TD]
[TD]
New​
[/TD]
[TD]
03/jan/17​
[/TD]
[TD]
74,Rej​
[/TD]
[TD]
FALSE​
[/TD]
[/TR]
</tbody>[/TABLE]


What am i missing?

M.
 
Upvote 0
Marcelo Branco said:
I downloaded your file, but didn't understand how did you get this output.


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D
​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD="bgcolor: #FFFF00"]
OUTPUT​
[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
State​
[/TD]
[TD]
Program​
[/TD]
[TD]
Picklisted​
[/TD]
[TD]
410 (A)​
[/TD]
[TD]
GRO1​
[/TD]
[TD]
GR1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
NSW​
[/TD]
[TD]
B​
[/TD]
[TD]
New​
[/TD]
[TD]
02/jan/17​
[/TD]
[TD]
2442​
[/TD]
[TD]
FALSE
​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
NSW​
[/TD]
[TD]
D​
[/TD]
[TD]
New​
[/TD]
[TD]
04/jan/17​
[/TD]
[TD]
2345​
[/TD]
[TD]
FALSE
​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
0​
[/TD]
[TD]
G​
[/TD]
[TD]
New​
[/TD]
[TD]
02/jan/17​
[/TD]
[TD]
71,74 Rej​
[/TD]
[TD]
FALSE
​
[/TD]
[/TR]
</tbody>[/TABLE]


According to your conditions i was expecting this

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
OUTPUT​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
State​
[/TD]
[TD]
Program​
[/TD]
[TD]
Picklisted​
[/TD]
[TD]
410 (A)​
[/TD]
[TD]
GRO1​
[/TD]
[TD]
GR1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
NSW​
[/TD]
[TD]
B​
[/TD]
[TD]
New​
[/TD]
[TD]
02/jan/17​
[/TD]
[TD]
2442​
[/TD]
[TD]
FALSE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
NSW​
[/TD]
[TD]
D​
[/TD]
[TD]
New​
[/TD]
[TD]
04/jan/17​
[/TD]
[TD]
2345​
[/TD]
[TD]
FALSE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
NSW​
[/TD]
[TD]
E​
[/TD]
[TD]
New​
[/TD]
[TD]
05/jan/17​
[/TD]
[TD]
5223​
[/TD]
[TD]
FALSE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
0​
[/TD]
[TD]
G​
[/TD]
[TD]
New​
[/TD]
[TD]
02/jan/17​
[/TD]
[TD]
71,74 Rej​
[/TD]
[TD]
FALSE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
QLD​
[/TD]
[TD]
S​
[/TD]
[TD]
New​
[/TD]
[TD]
08/jan/17​
[/TD]
[TD]
31,74, Rej​
[/TD]
[TD]
FALSE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
WA​
[/TD]
[TD]
4​
[/TD]
[TD]
New​
[/TD]
[TD]
03/jan/17​
[/TD]
[TD]
74,Rej​
[/TD]
[TD]
FALSE​
[/TD]
[/TR]
</tbody>[/TABLE]


What am i missing?

M.
Click to expand...

Hi Marcelo,

Two steps missing in above filter :-

1) After apply "New" Filter in Picklist, we need to apply filter in date column and in this column we need to remove latest 6 dates(I,e 5,7,8,9,10,12-jan).
2) in Column GR need to uncheck 74,Rej because we need to show values of Rej, if that cell contain 2 numbers like 71,74 Rej or 31,74, Rej.

After follow these 2 steps , I will get my desired result.

Regards,
Rajender
 
Upvote 0
Maybe something like this

1st Step: Checks the values in column GRO1
Copy/paste this User Defined Function (UDF)
Code: 
Function EvalCell(r As Range)
    Dim spl As Variant
    
    If Application.IsNumber(r.Value) Then
        EvalCell = "Y"
    ElseIf InStr(1, r.Value, "Rej", vbTextCompare) > 0 Then
            spl = Split(Application.Trim(Replace(Replace(r, ",", " "), "Rej", "")))
            If UBound(spl) > 0 Then
                EvalCell = "Y"
            Else
                EvalCell = "N"
            End If
    Else
            EvalCell = "N"
    End If
End Function


Then, assuming data in sheet Data A1:F25, let's create a helper column to check whether the values in column GRO1 are valid.
(VERDADEIRO in Portuguese = TRUE in English)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
State​
[/TD]
[TD]
Program​
[/TD]
[TD]
Picklisted​
[/TD]
[TD]
410 (A)​
[/TD]
[TD]
GRO1​
[/TD]
[TD]
GR1​
[/TD]
[TD]
Valid GRO1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Data​
[/TD]
[TD]
A​
[/TD]
[TD]
New​
[/TD]
[TD]
01/01/2017​
[/TD]
[TD]
aadd​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
N​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
NSW​
[/TD]
[TD]
B​
[/TD]
[TD]
New​
[/TD]
[TD]
02/01/2017​
[/TD]
[TD]
2442​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
NSW​
[/TD]
[TD]
C​
[/TD]
[TD]
New​
[/TD]
[TD]
03/01/2017​
[/TD]
[TD]
3444​
[/TD]
[TD]
VERDADEIRO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
NSW​
[/TD]
[TD]
D​
[/TD]
[TD]
New​
[/TD]
[TD]
04/01/2017​
[/TD]
[TD]
2345​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
NSW​
[/TD]
[TD]
E​
[/TD]
[TD]
New​
[/TD]
[TD]
05/01/2017​
[/TD]
[TD]
5223​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
0​
[/TD]
[TD]
G​
[/TD]
[TD]
New​
[/TD]
[TD]
02/01/2017​
[/TD]
[TD]
71,74 Rej​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
QLD​
[/TD]
[TD]
D​
[/TD]
[TD]
New​
[/TD]
[TD]
07/01/2017​
[/TD]
[TD]
4555​
[/TD]
[TD]
VERDADEIRO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
QLD​
[/TD]
[TD]
S​
[/TD]
[TD]
New​
[/TD]
[TD]
08/01/2017​
[/TD]
[TD]
31,74, Rej​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
QLD​
[/TD]
[TD]
SS​
[/TD]
[TD]
New​
[/TD]
[TD]
09/01/2017​
[/TD]
[TD]
7470​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
QLD​
[/TD]
[TD]
ER​
[/TD]
[TD]
New​
[/TD]
[TD]
10/01/2017​
[/TD]
[TD]
7470​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
WA​
[/TD]
[TD]
4​
[/TD]
[TD]
New​
[/TD]
[TD]
03/01/2017​
[/TD]
[TD]
74,Rej​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
N​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
WA​
[/TD]
[TD]
56​
[/TD]
[TD]
New​
[/TD]
[TD]
12/01/2017​
[/TD]
[TD]
7470​
[/TD]
[TD]
VERDADEIRO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
Data​
[/TD]
[TD]
A​
[/TD]
[TD]
Old site​
[/TD]
[TD]
01/01/2017​
[/TD]
[TD]
2334​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD]
NSW​
[/TD]
[TD]
B​
[/TD]
[TD]
Old site​
[/TD]
[TD]
02/01/2017​
[/TD]
[TD]
2442​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16​
[/TD]
[TD]
NSW​
[/TD]
[TD]
C​
[/TD]
[TD]
Old site​
[/TD]
[TD]
03/01/2017​
[/TD]
[TD]
3444​
[/TD]
[TD]
VERDADEIRO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
17​
[/TD]
[TD]
NSW​
[/TD]
[TD]
D​
[/TD]
[TD]
Old site​
[/TD]
[TD]
04/01/2017​
[/TD]
[TD]
2345​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
18​
[/TD]
[TD]
NSW​
[/TD]
[TD]
E​
[/TD]
[TD]
Old site​
[/TD]
[TD]
05/01/2017​
[/TD]
[TD]
5223​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
19​
[/TD]
[TD]
0​
[/TD]
[TD]
G​
[/TD]
[TD]
Old site​
[/TD]
[TD]
02/01/2017​
[/TD]
[TD]
71,74 Rej​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
20​
[/TD]
[TD]
QLD​
[/TD]
[TD]
D​
[/TD]
[TD]
Old site​
[/TD]
[TD]
07/01/2017​
[/TD]
[TD]
4555​
[/TD]
[TD]
VERDADEIRO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
21​
[/TD]
[TD]
QLD​
[/TD]
[TD]
S​
[/TD]
[TD]
Old site​
[/TD]
[TD]
08/01/2017​
[/TD]
[TD]
31,74, Rej​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
22​
[/TD]
[TD]
QLD​
[/TD]
[TD]
SS​
[/TD]
[TD]
Old site​
[/TD]
[TD]
09/01/2017​
[/TD]
[TD]
7470​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
23​
[/TD]
[TD]
QLD​
[/TD]
[TD]
ER​
[/TD]
[TD]
Old site​
[/TD]
[TD]
10/01/2017​
[/TD]
[TD]
7470​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
24​
[/TD]
[TD]
WA​
[/TD]
[TD]
4​
[/TD]
[TD]
Old site​
[/TD]
[TD]
03/01/2017​
[/TD]
[TD]
74,Rej​
[/TD]
[TD]
FALSO​
[/TD]
[TD]
N​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
25​
[/TD]
[TD]
WA​
[/TD]
[TD]
56​
[/TD]
[TD]
Old site​
[/TD]
[TD]
12/01/2017​
[/TD]
[TD]
7470​
[/TD]
[TD]
VERDADEIRO​
[/TD]
[TD]
Y​
[/TD]
[/TR]
</tbody>[/TABLE]


Formula in G2 copied down
=EvalCell(E2)

2nd step: gets the 6th largest date
Sheet Data

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
K​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
6th greatest date​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
05/01/2017​
[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in K2
=LARGE(IF(FREQUENCY(D2:D25,D2:D25),D2:D25),6)
Ctrl+Shift+Enter

3rd Step: Macro that uses Advanced Filter
Results in sheet Output

Code: 
Sub AdvFilter()
    
    With Sheets("Data")
       'Insert headers in sheet Output
       Sheets("Output").Range("C3:H3").Value = .Range("A1:F1").Value
       'Insert criteria in I1:I2
       .Range("I1") = ""
       .Range("I2").Formula = "=AND(C2=""New"",$K$2>D2,F2=FALSE,G2=""Y"")"
       'Apply Advanced Filter
       .Range("A1:G25").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Range("I1:I2"), CopyToRange:=Sheets("Output").Range("C3:H3"), Unique:=False
    End With
End Sub

Hope this helps

M.
 
Upvote 0
A much simpler solution (no need of helper column)

1.Create the UDF
Code: 
Function EvalCell(r As Range)
    Dim spl As Variant
    
    If Application.IsNumber(r.Value) Then
        EvalCell = "Y"
    ElseIf InStr(1, r.Value, "Rej", vbTextCompare) > 0 Then
            spl = Split(Application.Trim(Replace(Replace(r, ",", " "), "Rej", "")))
            If UBound(spl) > 0 Then
                EvalCell = "Y"
            Else
                EvalCell = "N"
            End If
    Else
            EvalCell = "N"
    End If
End Function

2. Insert the formula in K2 to get the 6th greatest date

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
K​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
6th greatest date​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
05/01/2017​
[/td][/tr]
[/table]

Array formula in K2
=LARGE(IF(FREQUENCY(D2:D25,D2:D25),D2:D25),6)
Ctrl+Shift+Enter

3. Apply Advanced Filter

Code: 
Sub AdvFilter()
    
    With Sheets("Data")
       'Prepare criteria in I1:I2
       .Range("I1") = ""
       .Range("I2").Formula = "=AND(C2=""New"",$K$2>D2,F2=FALSE,EvalCell(E2)=""Y"")"
       'Apply Advanced Filter
       .Range("A1:G25").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Range("I1:I2"), CopyToRange:=Sheets("Output").Range("C3"), Unique:=False
    End With
End Sub

M.
 
Upvote 0
Marcelo Branco said:
A much simpler solution (no need of helper column)

1.Create the UDF
Code: 
Function EvalCell(r As Range)
    Dim spl As Variant
    
    If Application.IsNumber(r.Value) Then
        EvalCell = "Y"
    ElseIf InStr(1, r.Value, "Rej", vbTextCompare) > 0 Then
            spl = Split(Application.Trim(Replace(Replace(r, ",", " "), "Rej", "")))
            If UBound(spl) > 0 Then
                EvalCell = "Y"
            Else
                EvalCell = "N"
            End If
    Else
            EvalCell = "N"
    End If
End Function

2. Insert the formula in K2 to get the 6th greatest date

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
K​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
6th greatest date​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
05/01/2017​
[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in K2
=LARGE(IF(FREQUENCY(D2:D25,D2:D25),D2:D25),6)
Ctrl+Shift+Enter

3. Apply Advanced Filter

Code: 
Sub AdvFilter()
    
    With Sheets("Data")
       'Prepare criteria in I1:I2
       .Range("I1") = ""
       .Range("I2").Formula = "=AND(C2=""New"",$K$2>D2,F2=FALSE,EvalCell(E2)=""Y"")"
       'Apply Advanced Filter
       .Range("A1:G25").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Range("I1:I2"), CopyToRange:=Sheets("Output").Range("C3"), Unique:=False
    End With
End Sub

M.
Click to expand...
Hi Marcelo,

Thanks a lot for your support , there is one issue I just found, if I apply it in my original data its not showing any output , if there are alphabetical values also in Date column in multiple rows and Large function showing error #N/A.

I tested the same in above given provided data as well same #N/A error, if alphabetical values also in data columns.
Could you please suggest.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
[VBA] File saving does not work/new version not uploaded
Replies
4
Views
727
NdNoviceHlp
NdNoviceHlp
A
  • Question Question
Filter by Date macro
Replies
6
Views
651
kevin9999
K
S
  • Solved
using advanced filter with 2 criteria one having a list & the other having text or no text
Replies
7
Views
825
RoryA
RoryA
L
  • Solved
Workflow Improvement
Replies
1
Views
780
Lehel
L
N
  • Question Question
Macro for Filter data
Replies
0
Views
423
Negi1984
N

Forum statistics

Threads
1,223,891
Messages
6,175,229
Members
452,621
Latest member
Laura_PinksBTHFT

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