+ Reply to Thread
Results 1 to 10 of 10

VBA for extracting different results based on multiple criterias

  1. #1
    Registered User
    Join Date
    08-15-2012
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    4

    VBA for extracting different results based on multiple criterias

    Dear forum,

    Since I am very new to VBA and programming in general i need some help with some VBA code that returns some results based on multiple criterias.
    In the worksheet I have attached I want to do the following:
    Considering the values in cells A, B and C (story, beam, load names) as the criterias (which I introduce in cells L, M and N) I want to fill the columns O to U with the coressponding values from cells D to J.
    The problem is how do I do this so that the code fills multiple rows since results are multiple for the same criteria.
    Since I have alot of data that keeps changing and which can be anywhere from 100 rows to 20000 rows I would also like to refill/refresh the result cells whenever dtat in the sheet changes.

    I tried an array formula, which I just drag diown, using countifs but it just takes ages for the cells to update when i chage the value of one criteria.

    Thank you in advance
    Attached Files Attached Files

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: VBA for extracting different results based on multiple criterias

    give this a try

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Registered User
    Join Date
    08-15-2012
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: VBA for extracting different results based on multiple criterias

    Works great. Thanly you very much Mike

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA for extracting different results based on multiple criterias

    Yeah, arrays are a bother when you try to apply them to an entire column like that. With datasets this large it will be less cumbersome on performance to use a helper column. See the attached, performance is returned, you can replace data in columns A:J, the key column K has formulas going down 20k rows and is still very quick and no VBA.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  5. #5
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: VBA for extracting different results based on multiple criterias

    @JBeaucaire

    Im not sure but I thinking the OP wanted to show results in the same row as the match was found. I dont know?

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA for extracting different results based on multiple criterias

    His original sheet (and problematic formulas) were in the LOCATION column on the right. Those are what I removed and replaced to return performance to the sheet.

  7. #7
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: VBA for extracting different results based on multiple criterias

    Yea your probably right? ;-). Im just not good with formulas, not that I havent tried.

  8. #8
    Registered User
    Join Date
    08-15-2012
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: VBA for extracting different results based on multiple criterias

    Thanks JBeaucaire, it's nice to have 2 different examples.
    Yes I wanted to show the data at the top of the spread sheet not where the match was found. I don't think it will bother me so much if I can copy the results returned to another spread sheet at the top in case I decide to go with the VBA version.
    I was planing to use one spread sheet to dump the data and then to put the criteria and obtain the results into another sheet, where I will use them for some calculations.

    Cheers

  9. #9
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: VBA for extracting different results based on multiple criterias

    Ok this will give the results at the top

    Please Login or Register  to view this content.
    and if you want to dumb these results to a different tab change this line
    Please Login or Register  to view this content.
    to and also can change O2 to A1 if you want
    Please Login or Register  to view this content.
    Last edited by mike7952; 08-29-2012 at 10:09 AM.

  10. #10
    Registered User
    Join Date
    08-15-2012
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: VBA for extracting different results based on multiple criterias

    Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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