Negi1984

Board Regular
Joined
May 6, 2011
Messages
198
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.
State ProgramPicklisted410 (A)GRO1GR1
0ANew7-Aug-132334FALSE
NSWBOld site24-Jul-132442FALSE
NSWCNew21-Oct-153444TRUE
NSWDNew15-Jan-152345FALSE
NSWEOld site3-Sep-155223FALSE
0GNew11-Jun-1471,74 RejFALSE
QLDDOld site29-Aug-134555TRUE
QLDSNew20-Sep-1331,74, RejFALSE
QLDSSOld site15-Jul-137470FALSE
QLDEROld site16-Jul-137470FALSE
WA4New17-Sep-1374,RejFALSE
WA56Old site12-Aug-137470TRUE

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

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.


C
D
E
F
G
H
2
OUTPUT​
3
State​
Program​
Picklisted​
410 (A)​
GRO1​
GR1​
5
NSW​
B​
New​
02/jan/17​
2442​
FALSE
7
NSW​
D​
New​
04/jan/17​
2345​
FALSE
9
0​
G​
New​
02/jan/17​
71,74 Rej​
FALSE

<tbody>
</tbody>


According to your conditions i was expecting this

C
D
E
F
G
H
2
OUTPUT​
3
State​
Program​
Picklisted​
410 (A)​
GRO1​
GR1​
4
NSW​
B​
New​
02/jan/17​
2442​
FALSE​
5
NSW​
D​
New​
04/jan/17​
2345​
FALSE​
6
NSW​
E​
New​
05/jan/17​
5223​
FALSE​
7
0​
G​
New​
02/jan/17​
71,74 Rej​
FALSE​
8
QLD​
S​
New​
08/jan/17​
31,74, Rej​
FALSE​
9
WA​
4​
New​
03/jan/17​
74,Rej​
FALSE​

<tbody>
</tbody>


What am i missing?

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



C

D

E

F

G

H

2

OUTPUT​

3

State​

Program​

Picklisted​

410 (A)​

GRO1​

GR1​

5

NSW​

B​

New​

02/jan/17​

2442​

FALSE

7

NSW​

D​

New​

04/jan/17​

2345​

FALSE

9

0​

G​

New​

02/jan/17​

71,74 Rej​

FALSE

<tbody>
</tbody>


According to your conditions i was expecting this


C

D

E

F

G

H

2

OUTPUT​

3

State​

Program​

Picklisted​

410 (A)​

GRO1​

GR1​

4

NSW​

B​

New​

02/jan/17​

2442​

FALSE​

5

NSW​

D​

New​

04/jan/17​

2345​

FALSE​

6

NSW​

E​

New​

05/jan/17​

5223​

FALSE​

7

0​

G​

New​

02/jan/17​

71,74 Rej​

FALSE​

8

QLD​

S​

New​

08/jan/17​

31,74, Rej​

FALSE​

9

WA​

4​

New​

03/jan/17​

74,Rej​

FALSE​

<tbody>
</tbody>


What am i missing?

M.

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)

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

<tbody>
</tbody>


Formula in G2 copied down
=EvalCell(E2)

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

K
1
6th greatest date​
2
05/01/2017​

<tbody>
</tbody>


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

K
1
6th greatest date​
2
05/01/2017​

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
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


K

1

6th greatest date​

2

05/01/2017​

<tbody>
</tbody>


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.
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

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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