CODEWITHSUNDEEP
excelvba
Abhijeet
Abhijeet

Reputation: 269

Not able to find largest value in column with reference to value in adjacent column

I want to find largest value in column B with reference to value in column A.InPUT

For example, i want to find maximum value in column B only for range where value in column A is "a" So, result in cells(1,3) be 100.

Then, i have to find max value for range where value in column A is "b". so, result on cells(2,3) be 110. and so on for next values.

Final result of macro should be:

enter image description here

I have written code for finding maximum value in column B as below,but unable to find max value by reference:

Sub my()

Cells(1, 3).Value = Application.WorksheetFunction.Max(Columns("B"))

End Sub

Please help.

Upvotes: 0

Views: 171

Answers (1)

MGP
MGP

Reputation: 2551

No need for macro:

{=MAX(IF(A$1:A$9999="a",B$1:B$9999))}

this will find the largest "a" value and

{=MAX(IF(A$1:A$9999="b",B$1:B$9999))}

this the largest "b"-value.

Please note, that this is an array formula, so enter it with Ctrl+Shift+Enter instead of the usual Enter, this means you don't have to type the {}. This only indicates, that this is an array formula.

Upvotes: 2

Related Questions