Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Lookup a column of values with the largest sum

  1. #1
    New Member
    Join Date
    Jun 2017
    Location
    Jonkoping, Sweden
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Lookup a column of values with the largest sum

    Hi there,
    Hope this post finds you well.
    I need to lookup cells from one of four columns, and place them in a new column. The lookup columns are p/l columns and I'm looking for the column with the max sum. So if column b has the max sum I want all the cells from that column to show up in column x.

    Kind Regards

    Per

  2. #2
    Board Regular
    Join Date
    Dec 2011
    Location
    The Netherlands
    Posts
    2,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup a column of values with the largest sum

    If I understand your question well, maybe like this.

    ABCDEF
    1result
    21164916
    383218
    41159611

    Blad9



    Worksheet Formulas
    CellFormula
    F2=MAX(A2:D2)


  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,991
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup a column of values with the largest sum

    I understood the request differently than Oeldere...

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    X1 X2 X3 X4
    2
    2
    1
    16
    4
    9
    X2
    3
    8
    3
    2
    1
    16
    4
    11
    5
    9
    6
    3
    5
    5
    6


    In G1 control+shift+enter, not just enter:

    =MATCH(MAX(MMULT(TRANSPOSE(A2:D4),ROW(A2:D4)^0)),MMULT(TRANSPOSE(A2:D4),ROW(A2:D4)^0),0)

    In G2 just enter:

    =INDEX(A1:D1,G1)

    In G3 just enter and copy down:

    =IFERROR(INDEX($A$2:$D$4,ROWS($G$3:G3),$G$1),"")
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    New Member
    Join Date
    Jun 2017
    Location
    Jonkoping, Sweden
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup a column of values with the largest sum

    Hey, thanks a lot guys!
    You nailed it Aladin.

    Kind regards
    /Per

  5. #5
    New Member
    Join Date
    Jun 2017
    Location
    Jonkoping, Sweden
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup a column of values with the largest sum

    Aladin,
    I'm using this in a table importing csv files.
    Is there a workaround so I can get this function on the same row?
    As for now I'm missing out the last number in the table.

    Kind regards
    /Per

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,991
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup a column of values with the largest sum

    Quote Originally Posted by Pille View Post
    Aladin,
    I'm using this in a table importing csv files.
    Is there a workaround so I can get this function on the same row?
    As for now I'm missing out the last number in the table.

    Kind regards
    /Per
    Care to specify what you mean according to the lay-out of post #3?
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    Jun 2017
    Location
    Jonkoping, Sweden
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup a column of values with the largest sum

    Quote Originally Posted by Aladin Akyurek View Post
    Care to specify what you mean according to the lay-out of post #3?
    G5 prints B4. The offset is not really a problem for looks. But in my table G5 will not print when I refresh.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,991
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup a column of values with the largest sum

    Quote Originally Posted by Pille View Post
    G5 prints B4. The offset is not really a problem for looks. But in my table G5 will not print when I refresh.
    Not over here. Are you having this problem with the set up of post #3 as is or with your own data for which you implemented the set up?
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    Jun 2017
    Location
    Jonkoping, Sweden
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup a column of values with the largest sum

    I only implemented the setup to my sheet of imported data.
    I'll build #3 and see if I'll get the same problem.

  10. #10
    New Member
    Join Date
    Jun 2017
    Location
    Jonkoping, Sweden
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup a column of values with the largest sum

    Quote Originally Posted by Aladin Akyurek View Post
    Not over here. Are you having this problem with the set up of post #3 as is or with your own data for which you implemented the set up?
    Ok checked and it works making a table with the above #3 including the empty cells.
    I'm using the query editor to import my data. I can manually add a row to that table, but when I hit refresh the new last row will be deleted.

    /Per

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com