Advance Filter Macro

[Negi1984]

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]

 

[Marcelo Branco]

See if this array formula works with your data

K2
=LARGE(IF(ISNUMBER(D2:D25),IF(MATCH(D2:D25,D2:D25,0)=ROW(D2:D25)-ROW(D2)+1,D2:D25)),6)
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Negi1984]

Hi Marcelo,

Now the error not coming even if any alphabetic field in Date column. But the output date which is showing here is incorrect. it should look for the largest 6th in column Picklisted under filter "New" Only. current its showing output of 6th largest values considering "New" & "Old site" both.

Note : It should also consider duplicate dates as well.

Regards,
Rajender

 

[Marcelo Branco]

Try this new version

=LARGE(IF(C2:C25="New",IF(ISNUMBER(D2:D25),IF(MATCH(D2:D25,D2:D25,0)=ROW(D2:D25)-ROW(D2)+1,D2:D25))),6)
Ctrl+Shift+Enter

M.

 

[Marcelo Branco]

The formula in post 13 needs an adjustment

Correction
=LARGE(IF(C2:C25="New",IF(ISNUMBER(D2:D25),IF(MATCH(IF(C2:C25="New",D2:D25),IF(C2:C25="New",D2:D25),0)=ROW(D2:D25)-ROW(D2)+1,D2:D25))),6)
Ctrl+Shift+Enter

M.

 

[Negi1984]

Hi Marcelo,

I have checked the data in provided file everything was working fine, but once I pasted the final original values in this test file it is not showing any output in "Output sheet".
Could you please tell me what I am doing wrong ?

I have attached once again the original file.

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

Regards,
Rajender

 

[Marcelo Branco]

Are you using Excel on-line or a desktop version?
I've no experience with Excel on-line.

M.

 

[Negi1984]

Hi Marcelo,

I am using Desktop version. you can download the file from link provided.

Regards,
Rajender

 

[Marcelo Branco]

Rajender,

Problems with your data
1. You told me that the criteria in column C should be "New", but in fact in your sheet the values are "New Site"
I had to correct the formula to use the proper value.

2. There are no numbers in column E (GRO1), only text
To fix it:
Select column E; go to Data > Text to columns; in the first step click in Finish
This should convert the "numbers" to real numbers

After this corrections, try this macro - it worked perfectly for me

Sub AdvFilter()
    Dim LR As Long
    
    With Sheets("Data")
        'Get lastrow with data
        LR = .Cells(.Rows.Count, "A").End(xlUp).Row
       'Prepare criteria in I1:I2
       .Range("I1") = ""
       .Range("I2").Formula = "=AND(C2=""New Site"",$K$2>D2,F2=FALSE,EvalCell(E2)=""Y"")"
       'Apply Advanced Filter
       .Range("A1:G" & LR).AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Range("I1:I2"), CopyToRange:=Sheets("Output").Range("C3:H3"), Unique:=False
    End With
End Sub

M.

 

[Marcelo Branco]

Remark: the macro above assumes that the headers (Data!A1:F1) do already exist in the range C3:H3 of Output. If they do not exist, try this macro (more robust):

Sub AdvFilter()
    Dim LR As Long
    
    With Sheets("Data")
        'Get lastrow with data
        LR = .Cells(.Rows.Count, "A").End(xlUp).Row
       'Prepare criteria in I1:I2
       .Range("I1") = ""
       .Range("I2").Formula = "=AND(C2=""New Site"",$K$2>D2,F2=FALSE,EvalCell(E2)=""Y"")"
       'Prepare headers in sheet output
       Sheets("Output").Range("C3:H3").Value = .Range("A1:F1").Value
       'Apply Advanced Filter
       .Range("A1:G" & LR).AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Range("I1:I2"), CopyToRange:=Sheets("Output").Range("C3:H3"), Unique:=False
    End With
End Sub

M.

 

[Negi1984]

Hi Marcelo,

Well noted your point, I have changed all the text numbers to numbers by text to column. but still there is a gap in output.
In output below result also showing :-

74702954-rejected

74704165-REJECTED

74703441- GRO REJECTED

74703964-REJECTED

I only need data with Rejected text only if numbers are double in it like 74703964 ,74703964 REJECTED or 74703964 ,74703964 -REJECTED. Could you please guide further. Regards, Rajender

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