Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 24 of 24

Thread: Lookup a column of values with the largest sum

  1. #21
    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

    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
    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
    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    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
    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.

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