VBA code for Two dimensional lookup (Rows & columns) with multiple criteria's.

    Hi,


    Seeking help in a creation of VBA code for Two dimensional lookup with multiple criteria.

    Three criteria's are in the summary sheet and one in the Display sheet in column D (variable).

    If all four criteria's matched in a JCB sheet than VBA will extract the data and paste in to the Display sheet AQ to AY column until there is no more criteria match in Display sheet column D.

    OK ... so after adding a second dimension ...


    Do you plan to add a third dimension ...;(8o;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Before diving into you new request ...


    Just a quick warning about your Worksheet_SelectionChange() ... since it is built quite ' dangereously '


    and could cause you many problems ...:/

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Just had a quick look at your latest request ...


    You are very seriously over-complicating your own life ...!!!


    At the initial stage, you should not worry about HOW the macro will be launched ... !!!


    1. Build a clear understandable workbook structure

    2. Keep in mind simplicity will make your life ( and maintenance ) easier

    3. Create the reports and macros you need

    4. and ... as a last step, think about how to run your macros ...


    By the way, for your own information ... all previous macros were already built with " two dimensional lookups " ...


    since, you needed to have a series of Offsets for the Columns ... and multiple criteria for the rows ... ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    You are absolutely right, I am 100% satisfied with your words. I'm clean in coding. I have no other choice. Just surfing and gathering code from many sources and trying to achieve my goal. By clicking on the cell, extract the required data from several sheets and paste them into one sheet.

    Understand you need the Results macro to be modified accordingly ...


    However, given the extreme confusion generated by your structure, could you add one complete example to illustrate:


    1. All the inputs and criteria


    2. The outputs and their location

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hello Carim...


    I can understand,should not do this while accepting experts advice....I'm compelled as this is the users requirement and I didn't aware with other method to bring the same output. However,I have given compleat example in Summary,JCB & Display sheets in the attached file above.

    If you need further explanation I will be happy to provide.

    Thanks in advance ...


    Because ... very honestly ... have spent quite a while trying to decode all your criteria, arrows, colors, sheets, etc ...


    and just want to give up ... !!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Make sure to attach an example which a 9 year old child can understand ....;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Quote from rehan kazi

    8|:D.......Will try8o


    With your first-class Spaghetti workbook ... you are facing a serious Challenge ... !!!8|:S;(

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi....


    By Click on Summary sheet cell C8 or D8, the following action will take place.


    First action: Match Summary sheet cell criteria C8, D8 & E8 with Lab Report Sheet column C, D & E. If matches, filter the match columns C, D&E, copy the filter data from specific columns (O6:AN column), paste them into the Display sheet D9:AD column.


    Second Action: Match Summary sheet cell criteria C8, D8 & E8 with Retrieval Sheet column C, D & E. If matches, filter the match columns C, D&E, copy the filter data from specific columns M6: M & Q6: Q columns (If columns C, D & E having duplicate criteria’s, then copy maximum value from Q6: Q) and paste them into the display sheet AE9: AF column, according to the dates in the Display sheet column D.


    Third Action: Match Summary sheet cell criteria C8, I8 & I6 and Display sheet column D dates with JCB Sheet row1,2 &3 and column B. If matches, filter the match column, copy the filter data from specific columns where below formula is true and paste them into the display sheet AQ9: AS column, according to the dates in the Display sheet column D.


    The following formula in the Display sheet column AQ,AR & AS, gives perfect output, need to be replaced with VBA code


    (formula for column AQ) INDEX(JCB!$C$6:$CB$500,MATCH(Display!D9,JCB!$B$6:$B$500,0),MATCH(Summary!$C$8&Summary!$I$8&Summary!$I$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0))


    (Formula for column AR) OFFSET(JCB!$B$5,MATCH(Display!D9,JCB!$B$6:$B$500,0),MATCH(Summary!$C$8&Summary!$I$8&Summary!$I$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0)+1,1,1)


    (Formula for column AS) OFFSET(JCB!$B$5,MATCH(Display!D9,JCB!$B$6:$B$500,0),MATCH(Summary!$C$8&Summary!$I$8&Summary!$I$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0)+2,1,1)



    Forth & Fifth Action: Will work like the fourth action, only the criteria cell of the summary sheet will be different


    For Forth Action, the Summary sheet cell criteria will be C8, K8 & K6 and output will come in Display sheet column AT, AU & AV

    For Fifth Action, the Summary sheet cell criteria will be C8, M8 & M6 and output will come in Display sheet column AW, AX & AY


    In attached sheet, placed the formulas in display sheets cell AQ9:AY13 , which gives the exact result ....what I am looking for


    Sixth Action: For Con sheet I guess no further explanation is required as you have already prepared the code for it .....in the module tab.....If so, I will explain...:P


    I hope you will get my explanation.

    Hi,


    As soon as I have a moment ... will give again into your maze ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Thanks in Advance....


    The whole idea is…..with a single click…. match multiple offset cell criteria's into multiple sheets….filter the rows, if criteria matched…..extract filtered data from multiple sheets….. paste (only values...no formatting) them into a single sheet.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login