Results 1 to 5 of 5

Thread: Returning corresponding cell(s) of those which have the greatest value

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Returning corresponding cell(s) of those which have the greatest value

    Hi everyone. Long time lurker, first time poster, this site and the users have provided me with great knowledge over the past few years, so thank you to everyone.

    I'm getting a bit deeper than I have needed in the past and need a little assistance b/c I could not find what I was looking for via forum search, nor could i muddle my way through the macro.

    What I am trying to do is below:

    1) Allow the user to input (or more preferably select from a drop down) a value that appears in Column D of my data worksheet. (Column D is comprised of Products.)

    2) Upon selecting the product (or manually typing it in), there needs to be a return of data in an adjacent cell which shows the area(s) with the highest number of units sold for that product. Area is column C, Total sales is Column J. If there are multiple areas with equal sales, both areas should be returned for the user.

    3) Data sample below:

    Irrelevant Irrelevant Area Item Type Irrelevant Irrelevant Irrelevant Irrelevant Irrelevant Total Sales
    x x North Petunias x x x x x 100
    x x South Roses x x x x x 98
    x x East Daisys x x x x x 97
    x x West Tulips x x x x x 96
    x x Northeast Petunias x x x x x 100
    x x Northwest Roses x x x x x 95
    x x Southeast Daisys x x x x x 94
    x x Southwest Tulips x x x x x 93



    If there is anything further you need from me to assist, please let me know.

    Thank you!

  2. #2
    New Member
    Join Date
    May 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Returning corresponding cell(s) of those which have the greatest value

    So for example, if a user chooses "Petunias" both "North" and "Northeast" would return; Choosing Roses would only return "South"

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,978
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Returning corresponding cell(s) of those which have the greatest value

    Welcome to Mr Excel forum

    Assuming your data in A1:J9, headers in row 1, maybe something like this


    L
    M
    N
    O
    P
    Q
    1
    Select Item
    LargestSale
    Count
    Area
    Item
    2
    Petunias
    100
    2
    North
    Petunias
    3
    Northeast
    Roses
    4
    Daisys
    5
    Tulips
    6


    Drop down in L2
    Create a unique list of items to use in Data Validation (Q2:Q5) - use Data > Remove duplicates

    Formula in M2
    =AGGREGATE(14,6,J2:J9/(D2:D9=L2),1)

    Formula in N2
    =COUNTIFS(D2:D9,L2,J2:J9,M2)

    Formula in O2 copied down
    =IF(N$2>=ROWS(O$2:O2),INDEX(C$2:C$9,AGGREGATE(15,6,(ROW(C$2:C$9)-ROW(C$2)+1)/((D$2:D$9=L$2)*(J$2:J$9=M$2)),ROWS(O$2:O2))),"")

    Hope this helps

    M.
    Last edited by Marcelo Branco; May 18th, 2019 at 10:13 AM.

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Returning corresponding cell(s) of those which have the greatest value

    Quote Originally Posted by Marcelo Branco View Post
    Welcome to Mr Excel forum

    Assuming your data in A1:J9, headers in row 1, maybe something like this


    L
    M
    N
    O
    P
    Q
    1
    Select Item
    LargestSale
    Count
    Area
    Item
    2
    Petunias
    100
    2
    North
    Petunias
    3
    Northeast
    Roses
    4
    Daisys
    5
    Tulips
    6


    Drop down in L2
    Create a unique list of items to use in Data Validation (Q2:Q5) - use Data > Remove duplicates

    Formula in M2
    =AGGREGATE(14,6,J2:J9/(D2:D9=L2),1)

    Formula in N2
    =COUNTIFS(D2:D9,L2,J2:J9,M2)

    Formula in O2 copied down
    =IF(N$2>=ROWS(O$2:O2),INDEX(C$2:C$9,AGGREGATE(15,6,(ROW(C$2:C$9)-ROW(C$2)+1)/((D$2:D$9=L$2)*(J$2:J$9=M$2)),ROWS(O$2:O2))),"")

    Hope this helps

    M.
    Thanks, Worked Perfectly. Its been a while since I have worked on in-depth excel formulas, so I gave you the wrong cells I was working off of so that it would force me to go through the formulas and understand exactly what I was doing. I have a solid understanding now and sincerely appreciate your time you took to reply.

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,978
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Returning corresponding cell(s) of those which have the greatest value

    You are welcome. Glad to help

    M.

Some videos you may like

User Tag List

Tags for this Thread

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
  •