1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'VBA Macros' started by Chirag R Raval, Nov 7, 2017.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    [​IMG]

    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
    Last edited: Nov 7, 2017
  2. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    Dear all,

    Can any one help regarding this thread?

    Regards,

    Chirag Raval
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    Please give a clear explanation in English of the criteria you have shown in the workbook.

    Narayan
  4. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    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
  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    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
  6. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    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
  7. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    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
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    It is clear now. Give me time to go through this.

    Narayan
  9. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    Dear Sir, @NARAYANK991

    Hope there are some solution.

    Regards

    Chirag Raval
  10. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    Dear Sir @NARAYANK991 ,

    Hope something can make the way.

    Regards,

    Chirag Raval
  11. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    See if this is what you wanted.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  12. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    Dear Sir @NARAYANK991 ,

    Thanks sir,
    I can not go at my job (office) todaytoday.
    I will revert soon with my findings.

    Regards,

    Chirag Raval
  13. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    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
  14. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

  15. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    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
  16. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    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
  17. Chirag R Raval

    Chirag R Raval Member

    Messages:
    382
    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

Share This Page