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

Formula for Advance filter on multiple criteria for separate each filtered instance as new file

C

Chirag R Raval

Member


Dear All,

Subject;- Formula for Advance filter on multiple criteria for separate each filtered instance as new file.

I have a code for advance filter & separate each filtered instance as an excel file.

This code filter data for each unique number entered under columns “U”.

You can also check yourself this success code (This code , Regards & always be thankful, for valuable Gift -forever useful, from this site, From helping experts on Chandoo.org) by put Heading as "Buy" at U1, & put buyer numbers under columns U (,U1 have heading) (any criteria heading on which you want to filter, criteria heading should must be same as database's column heading) in row 2 (U2) for test like byer no 11306 , 11305, 11302 after run code from VBA, From module 1, code generate files for that buyer nos..

Till now , code working fine for criteria for under U1 for particular heading to end …now there are requirement for add more criteria, require to cover more conditions

Advance filter till now run for on which ever under column “U”.

If there are formula in under columns “U”, then also advance fitter run for that formula…

But I can not set formula for that that cover more conditions mentioned at right side’s columns for filter

I want to cover conditions is for filter data for particular 1 buyer no between particular dates , & for particular type, particular quality no, particular date etc.

(for between date, Columns W’s heading is Confirmation Date & starting date like >10/10/2017 & column X’s heading also Confirmation Date for like <20/11/2017).

Particular types as you can see in attached requirement screen shot..

Its natural that Within this 4 criteria (Buyer No, between date, Particular type, particular quality) if any is blank , its cover all records for particular that heading .


I already attached screen shot of requirement & related data file.

Hope you can understand which I described here for just want formula or modification in code.

Regards,

Chirag Raval
 

Attachments

  • FOR CHANDOO.ORG-REQUIRE SEPARAE FILES ONLY FOR FILTERED UNIQUE RESULT'S SET (1) (1).xlsm
    25.4 KB · Views: 7
Last edited:
Dear Sir,

here explained..

(1) Attached file's code generate file for Buyer Numbers under column
U2 to U5

(2) for added more criteria beyond only just for buyer number,
require Modification in code .

(3) Columns U2, require formula, U3 require formula ,
U4,U5 require formula.

(4) Each cell with formula contain/cover/fulfil 5 criteria of
its right sides 5 columns .

(5) code generate separate files for that each formula instead its basically
construct for only buyer numbers.


hope this helps to understand requirements..

Regards,
Chirag Raval
 
Hi ,

I am sorry but I cannot make head or tail of what you have posted.

Why are there 4 criteria rows listed ?

Why can it not be one criteria row at a time ?

Can you please ask someone else who can articulate well to post the complete requirements , as otherwise I am afraid we will keep on going like this , trying to understand one post after the other.

Narayan
 
Dear Sir,

Exactly...1 criteria at a time... its okay & code runs for that way only..
as usual & natural..

picture display for just example of various possible criteria conditions for each runs only, that may be possible that many type of variations in criteria conditions on each run in right side row.........but definitely code run only for 1 under U2 & extract & separate file for all conditions at that cells right sides rows , & then run for U3 for its right sides criteria & then run for U4..

so each run its create separate file with all its right sides rows conditions.

may be its clear now. please mention if require more preciseness.

Regards,

Chirag Raval
 
Dear Sir

U2's Criteria is V2,W2, X2,Y2,Z2
U3's criteria is V3,W3, X3,Y3,Z3
U4's criteria is V4,W4,X4,Y4,Z4
U5th's criteria is V5,W5,X5,Y5,Z5

Regards,
Chirag Raval
 
Dear Sir,

Its Create files for all buyers though given only 1 buyer numbers in v2

okay , you just simply expand limited criteria range from only columns "U" to V1 to .Resize(, 5) that's towards basic facility of advanced filter.

That's approach I accepted & may be that's method is right for this requirement as multiple criteria range from columns from buyer number column (V) to expanded columns at right side (Z) in out thread.

now code runs for criteria V2 to Z2 & each run its separate files I accepted.

I ready to forgot for want formula which I want code runs only for that each formulas under in only columns "U" & may be VBA can not handle this approach.

but seems code runs for all buyer though given only 1 line criteria under V2 to Z2 , its create 4 files for each buyers also with without notice given criteria
its creates files as like no criteria given.

request Please look in to the matter .

Regards,
Chirag Raval
 
Dear Sir @NARAYANK991

Fantastic, really appreciated, work like a magic...and as desired.

can you spread some focus on roll of another coded range in this macro?

"Set crange = .Range("AH1:AH2").Resize(, 5)"

regards,
Chirag Raval
 
Chirag R Raval said:
Dear Sir @NARAYANK991

Fantastic, really appreciated, work like a magic...and as desired.

can you spread some focus on roll of another coded range in this macro?

"Set crange = .Range("AH1:AH2").Resize(, 5)"

regards,
Chirag Raval
Click to expand...
Hi ,

Since we are applying the criteria specified in the range V2:Z5 , one row at a time , we need to have a separate area of the worksheet dedicated to the criteria. This is because Excel does not accept the criteria in non-contiguous ranges i.e. we cannot have the criteria range header in V1:Z1 , and the criteria in V3:Z3.

So we have a separate area in the worksheet in columns AH:AL , where the header is in row 1 , and each time the criteria changes , it is copied to row 2 , so that the criteria range is always AH1:AL2.

Narayan
 
Dear Sir,

Amazing logic you applied...in short in for each loop , each time rows under columns "V" to "Z", copied in under "AH2" to "AL2", & code runs each times for criteria under "AH1" to "Al2" & separate files for those criteria..

Main forever useful point is we can expand & collapse this criteria range (columns-from database ) as we required...

Thank you very much for your invaluable effort for this thread...
also Thanks for all other experts & users & also for this site to contribute
spread knowledge & resolving problems in excel..

hope if another extra point raised related this situation/thread, there will be also found solutions from this full of knowledge environment.

Regards,

Chirag Raval
 
You must log in or register to reply here.
Back
Top