Taking Max values of Each column and analyze

Dear Masters,

i need one code for Extracting maximum values of some columns and analyze those values are Pass or fail by given condition.

please find the sample excel file and modify.

thanking you all,

Best regards.

Like so. the formulas in the colored columns are ARRAY formulas... ...any changes you make in the first cell at the top of each colored section must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

NOTE: I'm not sure you know this, but each time you "bump" your own thread, you are increasing the "replies" counter. When our answerers are looking at the lists of questions to answer and see a thread already has several replies, they usually do not even open the thread. So "bumping" your own question like you did here numerous times results in fewer and fewer people ever even "possibly" looking at it.

In short, do not reply in your own thread so quickly. Be patient, give it a couple of days, if necessary. Then maybe bump ONCE.

Also, some of our other member have commented to Admin about your use of Private Messages. Many do not like that at all and consider it a harassment. This is meant as a "for your information" so you are aware.

Originally Posted by JBeaucaire

Like so..

Dear sir,
thank you for kind reply and providing formulas. it is working. with help of your formulas not developing values at "M" Column., "M" column should be developed by your code. actual data file having huge rows and i have no idea about formulas.

So please make as a VBA Macro version. please find the attachment.

Thanking you,
Best Regards.

How huge?.

Originally Posted by JBeaucaire

How huge?.

thank you for your kind reply.

it is about A20000, some times it is upto A40000 (i.e. M to W columns out put data may be 1000 to 2000 rows)

if i executed by macro, no problem occurs by developing macro. that's why i have posted in Macro?VBA forum.so kindly convert your formula version to macro VBA version.


Thanking you,
Best regards.

Dear masters,

Our Respected Admin was provided by formula version, kindly convert to VBA Macro version.

Thank you in advance,
Best regards.

So you're saying you would need the column M values extracted for you as well?

What about those manual values off to the right? If we try to convert the formula version into a purely VBA version, how/when do those values in column W become available? VBA would do all the math in memory and simply write the results out to a flat table, no formulas would really be there. For a dataset as large as you're describing, that would be preferable. Array FORMULAS and lager datasesets don't play well together.

As noted in post #3, there will be few if any new eyes on this thread now that there are more than a couple of replies. So, for good or ill, it's you and I working on this. So patience is appreciated. We are on opposite sides of the globe, so it is doubtful we will be looking at this at the same time.

Originally Posted by JBeaucaire

So you're saying you would need the column M values extracted for you as well?

Dear Admin sir,

Thank you for Asking,

Yes sir, i need Macro for "M" Column also. here i have share code for "M" columns values generator from "A" column data, it is from my other code and remaining code is different with respect to my present input data. it is perfectly developing values at "M" Column.

so kindly add your formulas to below code. which is provided by you at post #2.

Please Login or Register  to view this content.

Thanking you all,

Best regards.

What about those manual values off to the right? If we try to convert the formula version into a purely VBA version, how/when do those values in column W become available? VBA would do all the math in memory and simply write the results out to a flat table, no formulas would really be there. For a dataset as large as you're describing, that would be preferable. Array FORMULAS and larger datasets don't play well together.

Originally Posted by JBeaucaire

What about those manual values off to the right? If we try to convert the formula version into a purely VBA version, how/when do those values in column W become available?

Dear Admin sir,

Thank you for your kind reply.

Manual values are i will paste from another sheet to "W" column. so before running your Macro VBA code i will prepare Manual data at "W" column. after that i will run the your code. Output data should be developed up to "M" column end data. so kindly prepare your amazing code to develop output.

Thanking you sir,
With best regards.

This little macro will:

1) extract all the column A values and drop them into column M
2) Add all my formulas into N:S 10 rows at a time then remove the formulas leaving the results behind
3) Add your formulas into T:U 10 rows at a time

If your column W values exist in each row, T:U will work as you designed, else you will see an error until you add those value.

These are array formula, I added 1000s of rows of data so you can see how slow this will be, but it works.

Originally Posted by JBeaucaire

This little macro will:

Dear sir,

Thank you very much for providing, while using your code showing errors values at cells and range N330 to U333 rows are un necessarily developed. because at "M" column data up to "M329" only. kindly find the sample attachment and test your code.


Thanking you,
Best regards.

@pvsvprasad

Post the xlsm file where the code is also available, so forummembers can see what has been done.

There is no code in your (added) file in #13.

Dear sir,
thank you for your kind suggestion.

please find attached xlsm file.

Thanking you,
Best regards.

1) The file I posted had several named ranges that have been removed in this file. Please use the file I uploaded that has the named ranges in them.
2) The file you uploaded does not have the required values in column A. The macro has been written based on your original posted file that has the BEAM number in every row in column A.

Originally Posted by JBeaucaire

1) The file I posted had several named ranges that have been removed in this file. Please use the file I uploaded that has the named ranges in them.
2) The file you uploaded does not have the required values in column A. The macro has been written based on your original posted file that has the BEAM number in every row in column A.

Really sorry sir,

i forgot to fill that "A" column. remaining code is i am not changed.

Rule is: Read data from "A" to "H" and "W" column in entire work sheet then produced Results at "M" to "U" columns.

please find updated File and modify the code.

Thanking you,
Best regards.

Drop your new data into the workbook I sent to you originally. It has named ranges installed in already.

Do not create a new workbook unless you also manually transfer all the named ranges. (CTRL+F3).

Simplest it to use the workbook I provided already as your template. It has the macro and the named ranges already installed.

Originally Posted by JBeaucaire

Drop your new data into the workbook I sent to you originally. It has named ranges installed in already.

Do not create a new workbook unless you also manually transfer all the named ranges. (CTRL+F3).
.

Dear sir,

Thank you for your kind reply.

as per your instructions i have followed your procedure and working well.small error only occurred, that is extra range of errors are produced from "N329" to "U333" with"#N/A" text

please find the attached file and Kindly fix this error.

*another request, is this possible direct code without pressing (CTRL+F3)? like a "Range("A4").Select" or other command, i dont know deeper how to insert these type of codes to macro (For automatic range selection).

rest of all your code is amazing, thank you for preparing.


With best regards.

All fixed.

Please Login or Register  to view this content.

Dear sir,

thank you for providing brilliant code.

i need small help. my 3 raw data columns are shifted. kindly find the attachment and make small modification.(i am tried but not getting logic behind your code)

and some of my raw data columns cells i got like a "N/A" instead of numerical values. and getting errors in output. kindly fix this minor error.

Thanking you,
Best regards.