+ Reply to Thread
Results 1 to 14 of 14

Filter multiple sheets based on 3 criteria and return results into single cell

  1. #1
    Registered User
    Join Date
    05-29-2017
    Location
    Perth
    MS-Off Ver
    2016
    Posts
    8

    Post Filter multiple sheets based on 3 criteria and return results into single cell

    Hi All,

    I'm wondering if someone might be able to help me with a VBA script to populate the 'yellow' cells in the attached document.
    Basically the script would search through the 5 sheets in the workbook and return all results in a single cell based on 3 criteria without duplicates.
    To further complicate the matter, although the criteria is consistent, the columns are not. All gray cells are hard coded including the gray cells in the 'Result' tab.
    Any help here would really be appreciated. I've had some success formulating this in excel, but it appears to be so slow it almost hangs up which is not practical. Each sheet will contain approximately 1,000 lines.
    Thank you in advance.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,897

    Re: Filter multiple sheets based on 3 criteria and return results into single cell

    See if this is how you wanted.
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-29-2017
    Location
    Perth
    MS-Off Ver
    2016
    Posts
    8

    Re: Filter multiple sheets based on 3 criteria and return results into single cell

    Thank you so much for this Jindon, you certainly are a wizard!

    The only thing is that Columns A, B and C are hard coded on the results sheet and the VBA should only populate columns D and E if a match is found. If no match is found then the cell would just be blank.
    Everything else is 100% perfect.

    Thank you for your help

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,897

    Re: Filter multiple sheets based on 3 criteria and return results into single cell

    OK, that would be much simpler.

    Im out at the momdnt, so I will post when I come back in about 3,4 hrs.

  5. #5
    Registered User
    Join Date
    05-29-2017
    Location
    Perth
    MS-Off Ver
    2016
    Posts
    8

    Re: Filter multiple sheets based on 3 criteria and return results into single cell

    Thanks so much Jindon,
    Also I did just notice that the column headers in the results sheet don't align with the data, but that issue should go away I imagine when you repost based on the hard coding of columns A, B and C. Column headers not required to be generated.

    Thank you

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,897

    Re: Filter multiple sheets based on 3 criteria and return results into single cell

    Try this one.
    Please Login or Register  to view this content.
    I'm just go out again, so next reply will be late if needed.

  7. #7
    Registered User
    Join Date
    05-29-2017
    Location
    Perth
    MS-Off Ver
    2016
    Posts
    8

    Re: Filter multiple sheets based on 3 criteria and return results into single cell

    Everything works perfect Jindon. Thanks for all your help.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,872

    Re: Filter multiple sheets based on 3 criteria and return results into single cell

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  9. #9
    Registered User
    Join Date
    05-29-2017
    Location
    Perth
    MS-Off Ver
    2016
    Posts
    8

    Re: Filter multiple sheets based on 3 criteria and return results into single cell

    Hi All,

    Sorry for reopening the thread here, but I noticed that I lacked some detail in the source file that i provided and now the script requires some editing.

    After running the above script, i noticed that the the VBA is clearing the gray formulated reference cells in the 'results sheet' (columns A, B and C) each time i run it. Can I retain these formulas?
    Also there are two result columns that are populated by the macro in the results sheet. This script will only populate a column when both columns have a value. There will be times where only one field will have a result. Is it possible to make it so that the two columns are not interdependent of each other?

    Thank you

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,897

    Re: Filter multiple sheets based on 3 criteria and return results into single cell

    Quote Originally Posted by matt2877 View Post
    After running the above script, i noticed that the the VBA is clearing the gray formulated reference cells in the 'results sheet' (columns A, B and C) each time i run it. Can I retain these formulas?
    Please Login or Register  to view this content.
    I don't understand the rest.
    It should update col.C & D regardless of they are filled or blank.

  11. #11
    Registered User
    Join Date
    05-29-2017
    Location
    Perth
    MS-Off Ver
    2016
    Posts
    8

    Re: Filter multiple sheets based on 3 criteria and return results into single cell

    So good, perfect every time. Thanks Jindon, this thread is now solved.

  12. #12
    Registered User
    Join Date
    05-29-2017
    Location
    Perth
    MS-Off Ver
    2016
    Posts
    8

    Re: Filter multiple sheets based on 3 criteria and return results into single cell

    Sorry, I have one more query here that i'd like to ask.
    After the script has been run, it changes my hard coded column headings on 'row 1' in the 'results' worksheet from 'Operator' and 'Location' to 'Date' and 'Shift'.
    Thanks.

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    17,897

    Re: Filter multiple sheets based on 3 criteria and return results into single cell

    Try change to
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    05-29-2017
    Location
    Perth
    MS-Off Ver
    2016
    Posts
    8

    Re: Filter multiple sheets based on 3 criteria and return results into single cell

    It works as intended, thank you.

+ 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. VLOOKUP to return multiple unique results in a single cell
    By EvolutionJulie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-09-2020, 11:50 AM
  2. Replies: 16
    Last Post: 02-21-2017, 02:10 AM
  3. return single value based on multiple criteria
    By ezzy2012 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-13-2014, 03:27 PM
  4. [SOLVED] How to return multiple values to a single cell based on multiple criteria
    By lwallace in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2014, 06:32 AM
  5. [SOLVED] lookup multiple values of a single cell and return sum of results
    By Zeppelin17 in forum Excel General
    Replies: 7
    Last Post: 08-10-2011, 07:09 PM
  6. Return Single Value Based on Multiple Criteria
    By grebgineok in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-15-2010, 01:36 PM
  7. Return results based on multiple criteria
    By Bryce in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-28-2005, 08: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