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

[jaseeffect]

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:

[TABLE="width: 694"]
<colgroup><col span="2"><col><col><col span="5"><col></colgroup><tbody>[TR]
[TD]Irrelevant[/TD]
[TD]Irrelevant[/TD]
[TD]Area[/TD]
[TD]Item Type[/TD]
[TD]Irrelevant[/TD]
[TD]Irrelevant[/TD]
[TD]Irrelevant[/TD]
[TD]Irrelevant[/TD]
[TD]Irrelevant[/TD]
[TD]Total Sales[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]x[/TD]
[TD]North[/TD]
[TD]Petunias[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]x[/TD]
[TD]South[/TD]
[TD]Roses[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]x[/TD]
[TD]East [/TD]
[TD]Daisys[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]97[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]x[/TD]
[TD]West[/TD]
[TD]Tulips[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]96[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]x[/TD]
[TD]Northeast[/TD]
[TD]Petunias[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]x[/TD]
[TD]Northwest[/TD]
[TD]Roses[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]x[/TD]
[TD]Southeast[/TD]
[TD]Daisys[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]94[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]x[/TD]
[TD]Southwest[/TD]
[TD]Tulips[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]93[/TD]
[/TR]
</tbody>[/TABLE]



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

Thank you!

 

[jaseeffect]

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

 

[Marcelo Branco]

Welcome to Mr Excel forum

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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[TD="bgcolor: #DCE6F1"]

O​

[/TD]
[TD="bgcolor: #DCE6F1"]

P​

[/TD]
[TD="bgcolor: #DCE6F1"]

Q​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Select Item
​

[/TD]
[TD]

LargestSale​

[/TD]
[TD]

Count​

[/TD]
[TD]

Area​

[/TD]
[TD][/TD]
[TD]

Item
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Petunias​

[/TD]
[TD]

100​

[/TD]
[TD]

2​

[/TD]
[TD]

North​

[/TD]
[TD][/TD]
[TD]

Petunias​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Northeast​

[/TD]
[TD][/TD]
[TD]

Roses​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Daisys​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Tulips​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[jaseeffect]

Welcome to Mr Excel forum

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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[TD="bgcolor: #DCE6F1"]

O​

[/TD]
[TD="bgcolor: #DCE6F1"]

P​

[/TD]
[TD="bgcolor: #DCE6F1"]

Q​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Select Item
​

[/TD]
[TD]

LargestSale​

[/TD]
[TD]

Count​

[/TD]
[TD]

Area​

[/TD]
[TD][/TD]
[TD]

Item
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Petunias​

[/TD]
[TD]

100​

[/TD]
[TD]

2​

[/TD]
[TD]

North​

[/TD]
[TD][/TD]
[TD]

Petunias​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Northeast​

[/TD]
[TD][/TD]
[TD]

Roses​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Daisys​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Tulips​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[Marcelo Branco]

You are welcome. Glad to help

M.