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

jaseeffect

New Member
Joined
May 18, 2019
Messages
3
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:

IrrelevantIrrelevantAreaItem TypeIrrelevantIrrelevantIrrelevantIrrelevantIrrelevantTotal Sales
xxNorthPetuniasxxxxx100
xxSouthRosesxxxxx98
xxEast Daisysxxxxx97
xxWestTulipsxxxxx96
xxNortheastPetuniasxxxxx100
xxNorthwestRosesxxxxx95
xxSoutheastDaisysxxxxx94
xxSouthwestTulipsxxxxx93

<colgroup><col span="2"><col><col><col span="5"><col></colgroup><tbody>
</tbody>



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

Thank you!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

jaseeffect

New Member
Joined
May 18, 2019
Messages
3
So for example, if a user chooses "Petunias" both "North" and "Northeast" would return; Choosing Roses would only return "South"
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

<tbody>
</tbody>


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:

jaseeffect

New Member
Joined
May 18, 2019
Messages
3
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

<tbody>
</tbody>


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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,783
Messages
5,470,738
Members
406,719
Latest member
ensbana

This Week's Hot Topics

Top