+ Reply to Thread
Results 1 to 29 of 29

Advanced filter - modifying formulae

  1. #1
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Advanced filter - modifying formulae

    hey guys,

    i need a small help,

    so i have a code which filters the cell value of sheet PRODUCT range "X2" and "Y2"
    It filters the value and displays the result
    "X2" filters the column "C"
    "Y2" filters the column "B"

    The code is
    Please Login or Register  to view this content.

    and

    Please Login or Register  to view this content.

    Now i want to combine these 2 formulaes so that it can filter on multiple condition.
    Now both the codes do not work together.


    I have attached my sample file for reference
    Attached Files Attached Files
    Last edited by anilpatni1234; 07-21-2018 at 01:41 PM.

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: Advanced filter - modifying formulae

    it can filter on multiple condition.
    Please give some examples
    Last edited by PCI; 07-21-2018 at 02:19 PM.
    - Battle without fear gives no glory - Just try

  3. #3
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: Advanced filter - modifying formulae

    Is it what you want
    Pay attention to the file attached
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    Hey,

    thanks for helping.

    Currently this code filters value of only one cell which is cell "Y2"
    Please Login or Register  to view this content.
    Now i want to filter the data based on multiple criteria .


    so now can i modify this code to filter the sheet based on cell values in cells "X2" "Y2","Z","AA,"AB,"AC"

    example:

    i want to filter the sheet with the following data

    "X2" contains - ""Bazar"
    "Y2" contains - "Ranjana"
    "Z2" contains - "Y"
    "AA" contains - "8000"

    Now i want to filter the sheet based on these cell values.



    So far for me , the code works for singe criteria
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    Hey PCI,

    not exactly.

    i am going to add different customers to this list everyday.

    So basically what i wanted was to filter the sheet by custom text.

    so example:

    i want to filter the sheet with the following data

    "X2" contains - ""Bazar"
    "Y2" contains - "Ranjana"
    "Z2" contains - "Y"
    "AA" contains - "8000"

    Now i want to filter the sheet based on these cell values.

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,403

    Re: Advanced filter - modifying formulae

    Maybe

    Please Login or Register  to view this content.
    Incidentally try to get into the habit of using dynamic named ranges rather than hard coding range addresses, since every time your data range changes, or were you to insert another column you'd need to modify the code.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,729

    Re: Advanced filter - modifying formulae

    See next code and file attached
    The range used for filter's Criteria has been moved to X15:AA16 to permit to see it when there is filter selection
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    hey Richard.

    Thank you so much. This code works perfectly .

    Thank you .

  9. #9
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    Hey PCI,

    Thank you so much.

    This code works perfectly.

  10. #10
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    Hey PCI,
    Hey Richard,


    I was thinking of placing the range used for filter's Criteria right at the top so that it becomes easier.

    In sheet "STOCK IN HAND" there is a code which filters data from sheet "PRODUCt"
    Here is the code

    Please Login or Register  to view this content.

    The problem is that this code clears the sheet everytime . If i type anything in cell a1 on sheet "Stock in hand" and change sheet for a second, then again if i come back to this sheet then the data gets cleared automatically.


    I was thinking of placing the range used for filter's Criteria right at the top of sheet "STOCK IN HAND"

    I have modified the code but its not working
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,403

    Re: Advanced filter - modifying formulae

    That's because you are clearing the used range (i.e. cells A1:AB11)

    But I'm not sure why you want to create the criteria range each time and then delete it.

    You've changed from filtering in place to copying to another range.
    The way I do this sort of stuff is :
    Create a dynamic range name for your data, say 'data'
    Create a range name for your criteria range, say 'crit'
    Create a range name for your output column labels, 'say 'data_out'

    Then the macro is simply

    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    please refer to next post
    Last edited by anilpatni1234; 07-22-2018 at 09:02 AM.

  13. #13
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    Hey guys ,

    I got his code to filter the sheet based on cell value in range "X1:AA2"
    Thanks to PCI and Richard for this code,


    Please Login or Register  to view this content.
    the problem is that when I type in the range "X1:AA2" , then it hides these ranges as well because it filters and displays the data from row 2 onwards.


    Is there any way to display the filered results from row 4 onwards?

    This will ensure that my range for filerting stays right at the top.

    I have attached my workbook
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Advanced filter - modifying formulae

    Change the filter range to start from row 4 instead of row 1

    Or see Richard's suggestion in post #11

  15. #15
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    Hey jason,

    Thanks,

    Did you mean to do this?
    Please Login or Register  to view this content.
    I tried this but it does not work.
    after autofilter is activated, the results are displayed from row 2 onwards.
    so it hides the filter range cell.

    So in post 7 you can see that PCI had moved the filter range down.

  16. #16
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Advanced filter - modifying formulae

    Insert 2 blank rows at the top of the sheet, move the criteria up into those rows.

  17. #17
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Advanced filter - modifying formulae

    anilpatni1234, with over 160 posts to your name, you really should know better.

    Rule 08: Cross-posting Without Telling Us

    Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    If you have less than 10 posts, do not try to copy and paste the link. Instead, type the link out in your thread.

    No further help to be offered, please, until the OP has complied with this request.

  18. #18
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    hey dominicb,

    I was not aware of the rule.

    I am really sorry about cross posting. The question has been cross posted .
    here is the link to it

    https://www.mrexcel.com/forum/excel-...ml#post5109447

    However i have requested them to delete that post

    I am unable to edit the first post as it says its blocked due to security reasons.


    Please do let me know If i can proceed to use this thread to get solutions
    Last edited by anilpatni1234; 07-22-2018 at 01:06 PM.

  19. #19
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,403

    Re: Advanced filter - modifying formulae

    You seem to be chopping and changing. Are you trying to filter the data or extract the filtered records to another area?

  20. #20
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    Hey Richard,

    Firstly sorry for confusing so much


    Basically I want to filter sheet "PRODUCT" based on column "IN STOCK"
    If cell matches "INSTOCK" then it is copied to sheet "stock in hand"

    This shows me the items which are stock in hand.

    Now I want to add filter options (custom filter based on cell value) in sheet "PRODUCT" and "STOCK IN HAND" .
    This makes it easier to find items which are stock in hand.


    Now i have decided to shift my table down (sheet PRODUCT) to place my custom filter range right at top (AS YOU SUGGESTED TO ADD BLANK ROWS)

    Now that i have shifted table down, my macro to filter isnt working

    Please refer to this thread https://www.excelforum.com/excel-pro...m-row-4-a.html
    Last edited by anilpatni1234; 07-22-2018 at 02:19 PM.

  21. #21
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    Hey Richard,
    this thread is solved.

    you can refer here https://www.excelforum.com/excel-pro...m-row-4-a.html


    Thank you so much for your constant support.

    THANK YOU SO MUCH

  22. #22
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Advanced filter - modifying formulae

    You don't seem to understand the forum rules very well do you?

    I already flagged your earlier thread as a duplicate and had it closed down by a moderator. Why would you think it acceptable to start another one?

  23. #23
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    hey jason,

    I am really confused .

    For this purpose i am marking this thread as solved.

  24. #24
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Advanced filter - modifying formulae

    The question you asked is a follow on from this thread so you should have kept it here, not started a new one.

    Regardless of whether or not you have marked the original thread as solved, the new one is still a duplicate. There is nothing confusing about it.

  25. #25
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    Hey guys.
    (THANKS TO MARC FOR THIS VBA CODE)
    (THANKS TO PCI and RICHARD FOR MODIFYING THE CODE FOR ME)


    I have this code to filter sheet "PRODUCT" based on column "IN STOCK"
    If cell matches "INSTOCK" then it is copied to sheet "stock in hand"

    Here is the code:
    Please Login or Register  to view this content.
    Now the problem is that the vba code clears the entire used range.
    If it possible that to add an exception - to not clear upto row 6?

    Because if i enter any data in row 1-6, then it autoclears it

  26. #26
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    hey guys,

    I tried removing
    Please Login or Register  to view this content.
    from the aboove code . it does not work.

    here is the code which i want to edit.
    Please Login or Register  to view this content.
    Now the problem is that the vba code clears the entire used range.
    If it possible that to add an exception - to not clear upto row 6?

    Because if i enter any data in row 1-6, then it autoclears it

  27. #27
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    Hey guys,

    Anyone please help me.

    I am unable to crack it

  28. #28
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Advanced filter - modifying formulae

    This question is now crossposted .

    here is the link

    https://www.mrexcel.com/forum/excel-...ml#post5110486

  29. #29
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Advanced filter - modifying formulae

    That is not the same thread number that you linked to in post #18.

    Does that mean that you are breaking the duplicate thread rule on mrexcel as well?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Advanced Filter Code : Criteria Range More Than 1 Row Breaks Filter
    By PaulGW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-10-2021, 04:30 PM
  2. Replies: 0
    Last Post: 08-04-2017, 02:39 AM
  3. VBA Advanced Filter - Two Filters Without Deleting Bottom Filter Data In Same Column Range
    By hysterical.useless in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2017, 02:54 PM
  4. Advanced Filter : One Criteria Cell Breaks the Filter - Returns Only Headers
    By PaulGW in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2014, 10:06 AM
  5. Replies: 5
    Last Post: 12-19-2013, 06:58 AM
  6. advanced filter - button to re apply advanced filter across multiple sheets
    By motmac87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2013, 11:16 PM
  7. [SOLVED] advanced filter a range:Advanced Filter function
    By Il Principe in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1