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,209
    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
    80,731
    Post Thanks / Like
    Mentioned
    5 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
    80,731
    Post Thanks / Like
    Mentioned
    5 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
    80,731
    Post Thanks / Like
    Mentioned
    5 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
  •  


DMCA.com