Page 3 of 3 FirstFirst 123
Results 21 to 24 of 24

Lookup a column of values with the largest sum

This is a discussion on Lookup a column of values with the largest sum within the Excel Questions forums, part of the Question Forums category; Sorry, correction. #16 should read A2:K1098. Otherwise the same until #20. Stupid me, type error. /...

  1. #21
    New Member
    Join Date
    Jun 2017
    Location
    Jonkoping, Sweden
    Posts
    15

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

    Sorry, correction. #16 should read A2:K1098. Otherwise the same until #20. Stupid me, type error.
    /

  2. #22
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,533

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

    Quote Originally Posted by Pille View Post
    No, A is just counting rows.
    /Per
    Is this row counting done by your query or some formula in A?
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #23
    New Member
    Join Date
    Jun 2017
    Location
    Jonkoping, Sweden
    Posts
    15

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

    Query

  4. #24
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    79,533

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

    Quote Originally Posted by Pille View Post
    Query
    Define Lrow in Formulas | Name Manager as referring to:

    =MATCH(REPT("z",255),data!$B:$B)

    Adjust the sheet name if your data lands in a different sheet.

    Define Nums in Formulas | Name Manager as referring to:

    =data!$D$2:INDEX(data!$K:$K,Lrow)

    Define Header in the Name Manager as referring to:

    =data!$D$1:$K$1
    __________________________________

    data (contains the data to process)

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    row count textfield-1 textfield-2 X1 X2 X3 X4 X5 X6 X7 X8
    2
    1
    jad damon
    1
    16
    4
    9
    3
    2
    vad nate
    8
    3
    2
    1
    4
    3
    nad joe
    11
    5
    9
    6
    5
    4
    wad john
    6
    20
    6
    5
    qad jon
    15


    processing

    Row\Col
    A
    1
    3
    2
    5
    3
    X3
    4
    4
    5
    2
    6
    9
    7
    20
    8
    15
    9
    10
    11
    12

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

    =MATCH(MAX(MMULT(TRANSPOSE(Nums)+0,ROW(Nums)^0)),MMULT(TRANSPOSE(Nums)+0,ROW(Nums)^0),0)

    In A2 of processing just enter:

    =COUNT(INDEX(Nums,0,A1))

    In A3 of processing just enter:

    =INDEX(Header,A1)

    In A4 of processing just enter and copy way down:

    =IF(ROWS($A$4:A4)>$A$2,"",INDEX(Nums,ROWS($A$4:A4),$A$1))
    Last edited by Aladin Akyurek; Jun 19th, 2017 at 04:36 PM.
    Assuming too much and qualifying too much are two faces of the same problem.

Page 3 of 3 FirstFirst 123

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