By the way ... forgot to mention ... the macro was modified to include the Column Offset (15 and 18) in the same Loop ...
Index match multiple criteria & autofill until last row with Column as ref
-
rehan kazi -
March 26, 2021 at 7:23 PM -
Thread is marked as Resolved.
-
-
-
Hi Carim,
Applied your version3 in my original sheet.....Work very nicely..... really appreciate your effort
-
Extremely pleased to hear this could be a relief in your work
-
Hi Carim,
It would be great if you could solve two more problems for me. In the same sheet you worked before.
First, by pressing the button, I want to extract the data from two more sheets. Secondly, If the search criteria finds a repeated matching criteria's , then the evaluate formula should pull the max of it.
Attached sheet, I marked the data I want to extract and also marked the criteria.
-
Let me try to find a path in the middle of so many search criteria and extracts ...
Once this is sorted out ... we will see how to tackle the second issue ...
-
-
Hope you will find good solution
-
Have already spent more than two hours ... to decode your logic ...
since you have not structured your different sheets in a way which would have created a smooth reporting ...
-
Hello Carim,
First of all, thank you so much for spending your precious time with me. Secondly, sorry for bothering you again and again.
I need another help from your side, you already understand my concept, so it would be better if you could suggest a better structure that is compatible for coding so I will start working on it.
-
Hello again,
If you are willing to restructure your workbook ... which is equivalent to almost starting from scratch ...
If it is an important project which deserves all the time you will have to invest ...you should consider attaching your workbook (without your confidential data ..)
BUT with the actual complete structure with all the sheets: Facility Index with 23 columns, Retrieval Report with 19 columns, Lab report with 52 columns, Bio with 10 columns , Con with 17 columns , etc ...
In addition, to select the best working method, it is important to know your approximate number of records ... since it can have an important impact on the overall performance ...
From a global perspective, the approach for Display to be effective should probably be built around AutoFilter ...
If you want to document yourself about this method ... see link below:
https://trumpexcel.com/vba-autofilter/
Hope this will help
-
Thank Carim,
Is it possible that I can share my original file with you by mail?
-
-
Hello,
The Forum offers in the top right menu a Conversations mode ... which might help
-
Hi Carim,
Thank you for your reply.... as you suggested, I am sharing actual complete structure with all the sheets with you. I have redesigned the macro code you provided as per my requirement, it is working fine now....but still I have two issues.
By clicking on one of the C7 to C729 summary sheet cells, the macro will run and copy data from multiple sheets and paste it into the display sheet but my VBA code performance is very slow. Is there any way to improve VBA performance and prevent slow code execution? And my second question is, In retrieval sheet there are repeated matching criteria's , so I want evaluate formula should pull the max of it.
I would be very grateful if you could solve my issues.
-
Hello,
As soon as I have a moment ... will take a moment to review your two requests : for the macro and the max ...
-
Thank you
-
Hello again,
Had a quick look at our file ... the main cause of all your problems does not lie in the macro ...
BUT ... there is a strangely coded event macro using SelectionChange in the sheet Summary ...
My first recommendation is to completely delete it ...
-
-
Regarding your second question about the Retrieval worksheet ... understand several records could match your criteria ...
So you do need to select the max ...
But the max of what ...???
Could you provide an example visually explicit ...
-
Hi...
In Summary Sheet Row No. 11, Column Headings C, D, E and Column9 containing the cell values are COD, CCP 13, NOC 17 and 31 May 20. If we click on the cell value CCP-13 or NOC-17, then the macro starts looking for all these 4 criteria's in the Retrieval sheet Column54,55,56 & column65...... finds two matches in the Retrieval sheet row 692 and row 693.In these two rows, the cell values on column 68 are 0.264 and 0.47. I want to extract the largest value from column68 Retrieval Sheet which is 0.47. But if you look at the Display sheet, the macro is extracting the smallest value 0.264 in column68 instead of the 0.47.
-
Hello,
Thanks for the very clear example ...
Will look into the modification ...
-
Sorry... again....and again.....
Also, if you make some changes to the summary sheet code to extract the same data as is currently being extracted from the Lab Result sheet and pasted into the display sheet, it will be very helpful to me.
-
Hello,
In order to extract the Max ... based on your multiple four dynamic criteria, the approach has to modified ...
In addition, since this very same approach is used for your five different cases ...
Before diving into the modifications, it is important to be very precise as to which specific cases do require this modification
The example you have provided is related to your Columns 31 and 32 ....
Could you confirm extracting the Max is only applicable to these two Columns ...
...and, since you keep on adding questions on top of questions ... when you refer to the worksheet Summary ... are you asking for the same MAX formula to be applied to the cell Z11 ... ???
Thanks
-
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!