Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: Lookup a column of values with the largest sum

  1. #11
    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
    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
    Let's separate data (input) and processing (output)...

    data (name of the data sheet)

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


    processing (the name of the processing sheet)

    Row\Col
    A
    1
    2
    2
    X2
    3
    16
    4
    3
    5
    5
    6


    In A1 of processing control+shift+enter, not just enter:

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

    In A2 of processing just enter:

    =INDEX(data!A1:D1,A1)

    In A3 of processing just enter and copy down:

    =IFERROR(INDEX(data!$A$2:$D$4,ROWS($A$3:A3),$A$1),"")

    If you get the problem when you do a new import, we can implement dynamic named ranges in order not to miss any change to the data area in the data sheet if you like...
    Assuming too much and qualifying too much are two faces of the same problem.

  2. #12
    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

    Thanks,
    I guess it is a problem with formulas when you update the input data. The input range is not static. So when I import a data stream with more rows than before the formula columns does not print on the extra rows. Hmmm..

  3. #13
    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
    Thanks,
    I guess it is a problem with formulas when you update the input data. The input range is not static. So when I import a data stream with more rows than before the formula columns does not print on the extra rows. Hmmm..
    The formulas process the ranges they are fed with. So the ranges must be up to date, hence dynamic named ranges.

    What is the current range that the data occupies?
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #14
    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

    Ok I get it. My ranges are from 500 to maybe 20 000 rows. Source is csv through query, works fine.
    The one I build with is 1500 rows.
    Kind Regards
    Per

  5. #15
    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
    Ok I get it. My ranges are from 500 to maybe 20 000 rows. Source is csv through query, works fine.
    The one I build with is 1500 rows.
    Kind Regards
    Per
    I'm asking for the exact current range...
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #16
    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

    Ok,
    It's A2:I1098
    /Per

  7. #17
    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
    Ok,
    It's A2:I1098
    /Per
    Are the particular ranges in the columns A:I always equally filled in with numeric data?
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #18
    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

    Column B and C is text. But I can live without them if necessary. They only tell me what I'm working with. They are all equally filled, always.

    /Per

  9. #19
    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
    Column B and C is text. But I can live without them if necessary. They only tell me what I'm working with. They are all equally filled, always.

    /Per
    That's great. Is A, which is numeric, relevant for the processing? If relevant, are you able to have the text columns in A and B (instead of B and C) so that we only have numeric data area in C:I?
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #20
    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

    No, A is just counting rows.
    /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