Results 1 to 7 of 7

Thread: Need formula for this

  1. #1

    Need formula for this



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Can someone help me please with this in Excel 2010:

    In the example of the attached picture:

    Example with yellow cells: numbers in A1 and B1 to be detected on the columns E, F, I, J (cells A1 and B1 are always side by side in this way), then these two numbers to be displayed in the cell M3 plus the maximal of the numbers from the cells: C1, G2 and K4 (neighboring cells). The final text (cell M3) must look exactly like in the picture.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Example.jpg 
Views:	22 
Size:	49.8 KB 
ID:	4152  

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Cross posted at msofficeforums. Please read this

  3. #3
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by Mike91 View Post
    In the example of the attached picture:
    Why you not add a source file, not picture?

    If you not bother extra columns, then try to solve the problem in the following way

    example:

    The columns S, T, U (first cell)
    Copy => Paste Special => Paste Link

    cell W2
    ARRAY formula
    Code:
    =IFERROR(INDEX(S$2:S$13;MATCH(0;COUNTIF(W$1:W1;S$2:S$13);0));0)
    The cell X2
    VLOOKUP formula
    Code:
    =VLOOKUP(W2;$S$2:$T$13;2;FALSE)
    cell Y2
    ARRAY formula
    Code:
    =MAX(((S$2:S$13=W2)*U$2:U$13))
    Finally cell M1
    Put formula
    Code:
    =CONCATENATE(W2;";";X2;";";Y2)
    Of course this can be solved in a different way in the column W.
    The first extra column (instead of S-T-U columns)
    Unique from three columns
    Other columns as described above

    Pay attention! You may need to use a comma instead of a semicolon
    Last edited by navic; 2015-11-13 at 08:48 AM.
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  4. #4
    I attached the source file (Example.xlsx)

    When I try to use formula for W2 it give me error at this point: "S$13"
    Formula for X2 give error at this point: "W2"
    Formula M1 give an error at this point: "W2"

    Anyway, thanks for trying to help me.
    Attached Files Attached Files

  5. #5
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013

    Concatenate Unique with conditions from multiple columns

    Quote Originally Posted by Mike91 View Post
    When I try to use formula for W2 it give me error at this point: "S$13"
    I wrote that it ARRAY formula
    This is the so-called CSE formulas, Ctrl+Shift+Enter (not just enter)

    BTW: Title of your post does not reflect a problem that you have

    See my attachment
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  6. #6
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Please add a link to your cross-posts. You get free help here, have a little respect for those who are here for FREE on their FREE time to help YOU !

  7. #7
    With the help of your exemple, I made this for cell M1:

    {=A1&";"&B1&";"&MAX(IF(IFERROR(VLOOKUP(A1,E1:G:G,2,FALSE)=B1,0),MAX(C1,VLOOKUP(A1,E1:G:G,3,FALSE)),0),IF(IFERROR(VLOOKUP(A1,I1:K:K,2,FALSE)=B1,0),MAX(C1,VLOOKUP(A1,I1:K:K,3,FALSE)),0))}

    Thank you very much for help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •