• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter column grater than 7 days ageing

Vijayarc

Member
Hi Ninja

I want macro to filter column which is grater than 7 days ageing , trick part is
columns value will be like, mixed up

Completed - 14 Days
Completed - 0 Days
Rejected - 84 Days
Rejected - 103 Days
Completed - 0 Days
Rejected - 84 Days
WIP - 601 Days
NotStarted - 684 Days
NotStarted
NotStarted - 84 Days

I want to filter only WIP-601 days 'NotStarted', NotStarted - 684 Days
(all days grater than 7 days only)

please find my sample file in 'M , N 'column I need to filter
kindly help and do needful
 

Attachments

  • Batch Report_Daily.XLSM
    586.2 KB · Views: 4
  • db.xlsx
    43.1 KB · Views: 3
Vijayarc
I want to filter only WIP-601 days 'NotStarted', NotStarted - 684 Daysfrom which file and from which column
would find those WIP-601 days ...
which You would like to filter?
Could You show some samples?
 
Hi Vketm

please find attached screenshot, in my Batch_Report sheet file above In that data has
Not-started -2 days, Not started-6 days should not filter, its less than 7 days

I want to filter data grater than 7 days only
filter should done in 'M and N column
Code:
  Selection.AutoFilter
  Columns("Z:BC").Select
  Selection.Delete Shift:=xlToLeft
Selection.AutoFilter
  ActiveSheet.Range("A1:Q1").AutoFilter Field:=9, Criteria1:=Array( _
  "WIP - LOB", "Rejected"), Operator:=xlFilterValues

' filter M , N columns ageing grater than 7 days
In my code scree shot , I done wildcard search filter, but I show all data less then 0 ,2, 6 days also,
please find below another screen shot were filter code should come

kindly help and do needful.
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    61.6 KB · Views: 14
  • Batch Report.PNG
    Batch Report.PNG
    193 KB · Views: 11
Last edited:
Vijayarc
Your criteria seems vary ...
[ Do It ] filters ...
>> i-column: 'WIP - LOB' or 'Rejected'
and
>> m&n-columns: 'NotStarted * Days > 7'
 

Attachments

  • Batch Report_Daily.XLSM
    657.8 KB · Views: 14
HI Vletm

Big sorry for confusion !!!! thanks for above try,
I am doing as
when we click upload Dashboard button,, and upload the db file and then based on macro and filters for each sheets modules, Daily_Prod, Post_Rel , Saui_Ofac,
filtration will done and pasted on each file and later I will email it on each button click.
.
on your file, u done filter on Batch Report itself, but I need M and N filter only
on Daily_Pro sheet .
different column filters for Saudi_Ofac sheet and others,

the code u given should come on module 1 for Daily_Prod sheet as per attached screen, but I am getting error

please help and do needful, sorry for confusion once again!!
Capture.PNG
 
Vijayarc ... hmm?
... and next,
You would like to fly to the moon?

Now You start writing something 'when we click upload Dashboard button' - Yes!
Did You explain that clear before?
(#1)
I want macro to filter column which is grater than 7 days ageing , trick part is
columns value will be like, mixed up
Completed - 14 Days
Completed - 0 Days
Rejected - 84 Days
Rejected - 103 Days
Completed - 0 Days
Rejected - 84 Days
WIP - 601 Days
NotStarted - 684 Days
NotStarted
NotStarted - 84 Days
I want to filter only WIP-601 days 'NotStarted', NotStarted - 684 Days
(all days grater than 7 days only)
please find my sample file in 'M , N 'column I need to filter

(#3)
in my Batch_Report sheet file above In that data has
Not-started -2 days, Not started-6 days should not filter, its less than 7 days
I want to filter data grater than 7 days only
filter should done in 'M and N column

Cannot You copy or move that my done macro ... somewhere?
It should work with other sheet ... if structure of sheet is same.
Why it should belong to Module1?
Hint ... if You copy paste 'my code' into other code,
then You should know,
what 'my code' do and what need to modify!
I won't do that myself!
... and that screenshot ... no way! I've none positive comment!
That would be more than miracle, if it would start working!

What would mean Your 'do needful'?
 
the code is not working as per my request
my condition is
I - columns should filter 'WIP - LOB' and 'Rejected'
M. N column should filter on TS and Credit header, i want to filter the condition as: 'NotStarted, Notstarted - 68 days' (all days should be grater then 7 days)
'Notstarted - 2 days' - should not filter, because it less than 7 days
Notstarted - 0 days' - should not filter, because it less than 7 days
Notstarted - 6 days' - should not filter, because it less than 7 days
Notstarted - 7 days' - should filter, because it equal to 7 days

please help and advise how to filter for above condition
i want the code should place on module 1- below the filter condition code as per image for my understanding, the same code i wan to respect for other sheets and columns
 
Vijayarc
1) Which is not working?
2) Have You change Your 'requirements' again?
eg where comes ... TS and Credit header ?
3) Where is that module 1? And many times asked ... why?
4) How possible this? ... as per image for my understanding
 
1)I placed my 1st condition under module 1 , as per image above
Selection.AutoFilter
ActiveSheet.Range("A1:Q1").AutoFilter Field:=9, Criteria1:=Array( _
"WIP - LOB", "Rejected"), Operator:=xlFilterValues

I request the second condition on module 1

2).M ,N - Column header is only TS and Credit header
3)Where is that module 1? And many times asked ... why?
because if code come in module- for my understanding I will repeated many 20 module , to generate 20 emails.
4. already I given db file and my master file , -pls check if given code working
for my conditions

if code works pls advise how to repeated the same code for all my future 20 sheets like, Post_Rel, Saudi_Ofac ...... to filter same conditions grater than 7 days but different columns
 
Vijayarc
1) If You have not read any of my replies then You would know that Your way won't work!
2) no answer!
3) Do You mean this ... I added my the newest version of my code there ...
Screen Shot 2018-08-06 at 12.51.57.png
and again no answer to my question
4) You write something ... but not give any answers ...
> While using 'my code', the whole code need to use! <
ps . I gotta use Your snapshot with code ... because not better code from You!
 
hi vletm

as per the given code the M column is not filtering as per condition
I tried all possibilities but only "M" column not filtering .
please check if done any wrong
 

Attachments

  • Batch Report_Daily.XLSM
    434.6 KB · Views: 2
Vijayarc
I've no time to guess how did You test or use that!

I modified that 'Module1' code
You have to take care that before run Do_It
ActiveSheet have to be that sheet which You would like to filter!
(and that uses AA-column for filtering!)

M & N-column matter ONLY if 'NotStarted*' otherwise no action!

I left 'Daily_Pro'-sheet filtered as You have given 'rules'.
If Your 'rules' has changed ... who knows?
 

Attachments

  • Batch Report_Daily (1).XLSM
    477.4 KB · Views: 8
Thank u Vletm

Today I really feels as flying to moon, it worked at last!!!!!

Thanks you for grate valuable support and for your patience.

:)
:awesome::awesome::awesome:thanks again for Chandoo:):)
 

Attachments

  • Batch Report_Daily.XLSM
    88.2 KB · Views: 2
HI Vletm

I came one issue today as, if condtion not met- that is: if word 'NotStarted"
not found in filter, the loop going to infinite rows. and am getting only infinite blank rows and excel hangs
pls check and advise
 
HI Vletm -
M & N-column matter ONLY if 'NotStarted*' otherwise no action!
instead of no action
the line should come as
" No Case ID is grater than 7 days ageing " in row1 as highlight

is it possible!!
 
Hi Vletm
please find attached file ,

the line should come as " No Case ID is found grater than 7 days ageing " in row1 as highlight
 

Attachments

  • Batch Report_Daily_2LOB_Working.XLSM
    342 KB · Views: 2
Vijayarc
I didn't figure 'You challenge'!
You didn't write.. what and where as someone else would guess it!
1) If You use that code other way as I've written .. it's Your choice!
I cannot and won't guess how You use it!
2) I added my version of 'Do_It' to Sheet1 and run it with four sheets.
Only 'something' is in 'Global Trade'-sheet, there seems to be 23 rows ...
Maybe Your 'cleaning' won't work 100% ... only You can guess that!
3) If there are none which match with You named filtering then
only 1st row will stay visible from 'rows which has data'.
4) I didn't find any loop nor hangs with 'Do_It'-macro!
 

Attachments

  • Batch Report_Daily_2LOB_Working.XLSM
    373.2 KB · Views: 6
Hi vletm !

Maybe an idea to avoid any forum abuse for the future next threads :
if the original post is not clear enough, needing more than a single
question to ask, do not answer until the thread creator post again
then just ask for a complete explanation and attachment accordingly.
If next post still needs questions, ask only a question at a time,
no more as you yet often saw when several questions are asked,
few get any answer …
Propose a solution until all is well defined, crystal clear …
Maybe for his next thread the author will pay attention as it must
to create a correct thread and if not, he will enter in the same process :
waiting - no answer - up - answer just asking a complete explanation
and an attachment - post asking a question only …
Like as an education for children …

As a reminder, any answer is at the level of the initial post :
the better explanation & attachment, the better & quicker solution !

As time is on the helpers side …
 
Marc L
Many times, if write more than one line, I'm not sure which one will read!
.. and after some questions ... someone else would give shorter solution.
Of course, it's more than possible after digging those answers - details.
There are many ways to do these and
I'll do these 'my way' as You have noticed;)
 
No, just post only a question and nothing more until
you get a valid answer to your question then
you can create the next question post, as time is on our side …

Like the Doctor, we are the Time Masters !
 
hi Vletm
I required one condition as

in above we filter grater than 7 days
in your DO_it macro
after filter , I want to highlight the rows in red, which is grater than 30 days
 
my end motto to generate email,, if data not found in filter and sheet is blank,
In Email generation button I kept message box, 'No transaction meet criteria" as per attached image, as I solved (#18 Reply)

I want now (reply #23) is on high priority, I tried but no hope , please if you try
 

Attachments

  • email.PNG
    email.PNG
    94.4 KB · Views: 6
  • Batch Report_Daily_LOB_Compl7tew.XLSM
    334.8 KB · Views: 3
Back
Top