+ Reply to Thread
Results 1 to 22 of 22

Taking Max values of Each column and analyze

  1. #1
    Forum Contributor
    Join Date
    12-16-2013
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    215

    Question 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.
    Attached Files Attached Files
    Last edited by pvsvprasad; 08-27-2016 at 10:47 AM.

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

    Re: Taking Max values of Each column and analyze

    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.
    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!)

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

    Re: Taking Max values of Each column and analyze

    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.
    Last edited by JBeaucaire; 08-28-2016 at 07:34 PM.

  4. #4
    Forum Contributor
    Join Date
    12-16-2013
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    215

    Question Re: Taking Max values of Each column and analyze

    Quote Originally Posted by JBeaucaire View Post
    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.
    Attached Files Attached Files
    Last edited by pvsvprasad; 08-27-2016 at 10:48 AM.

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

    Re: Taking Max values of Each column and analyze

    How huge?.

  6. #6
    Forum Contributor
    Join Date
    12-16-2013
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    215

    Re: Taking Max values of Each column and analyze

    Quote Originally Posted by JBeaucaire View Post
    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.
    Last edited by pvsvprasad; 08-28-2016 at 03:22 AM.

  7. #7
    Forum Contributor
    Join Date
    12-16-2013
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    215

    Re: Taking Max values of Each column and analyze

    Dear masters,

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

    Thank you in advance,
    Best regards.
    Last edited by pvsvprasad; 08-28-2016 at 09:00 AM.

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

    Re: Taking Max values of Each column and analyze

    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.
    Last edited by JBeaucaire; 08-28-2016 at 07:36 PM.

  9. #9
    Forum Contributor
    Join Date
    12-16-2013
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    215

    Re: Taking Max values of Each column and analyze

    Quote Originally Posted by JBeaucaire View Post
    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.
    Last edited by pvsvprasad; 08-29-2016 at 01:23 AM.

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

    Re: Taking Max values of Each column and analyze

    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.

  11. #11
    Forum Contributor
    Join Date
    12-16-2013
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    215

    Re: Taking Max values of Each column and analyze

    Quote Originally Posted by JBeaucaire View Post
    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.
    Last edited by pvsvprasad; 08-29-2016 at 02:39 AM.

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

    Re: Taking Max values of Each column and analyze

    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.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    12-16-2013
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    215

    Re: Taking Max values of Each column and analyze

    Quote Originally Posted by JBeaucaire View Post
    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.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by pvsvprasad; 08-29-2016 at 12:21 PM.

  14. #14
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Taking Max values of Each column and analyze

    @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.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  15. #15
    Forum Contributor
    Join Date
    12-16-2013
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    215

    Re: Taking Max values of Each column and analyze

    Dear sir,
    thank you for your kind suggestion.

    please find attached xlsm file.

    Thanking you,
    Best regards.
    Attached Files Attached Files
    Last edited by pvsvprasad; 08-29-2016 at 02:49 PM.

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

    Re: Taking Max values of Each column and analyze

    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.

  17. #17
    Forum Contributor
    Join Date
    12-16-2013
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    215

    Re: Taking Max values of Each column and analyze

    Quote Originally Posted by JBeaucaire View Post
    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.
    Attached Files Attached Files
    Last edited by pvsvprasad; 08-29-2016 at 11:38 AM.

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

    Re: Taking Max values of Each column and analyze

    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.

  19. #19
    Forum Contributor
    Join Date
    12-16-2013
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    215

    Re: Taking Max values of Each column and analyze

    Quote Originally Posted by JBeaucaire View Post
    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.
    Attached Files Attached Files
    Last edited by pvsvprasad; 08-30-2016 at 12:13 AM.

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

    Re: Taking Max values of Each column and analyze

    All fixed.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  21. #21
    Forum Contributor
    Join Date
    12-16-2013
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    215

    Re: Taking Max values of Each column and analyze

    Quote Originally Posted by JBeaucaire View Post
    All fixed.
    Yes sir,
    all fixed and well executed. thank you master


    Best regards.

  22. #22
    Forum Contributor
    Join Date
    12-16-2013
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    215

    Re: Taking Max values of Each column and analyze

    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.
    Attached Files Attached Files
    Last edited by pvsvprasad; 09-02-2016 at 10:40 AM.

+ 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. Replies: 6
    Last Post: 09-07-2015, 02:48 PM
  2. [SOLVED] How to make EXCEL sort, analyze and sum up values
    By sardolik in forum Excel General
    Replies: 5
    Last Post: 01-28-2015, 04:15 PM
  3. [SOLVED] Autofill along a row, taking values from every second column
    By QuantumDude in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-11-2013, 08:20 AM
  4. Replies: 2
    Last Post: 05-13-2011, 07:08 AM
  5. Analyze data in one column and sort it in another column
    By gerryger in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-10-2009, 11:44 PM
  6. Replies: 0
    Last Post: 03-08-2006, 11:47 AM

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