Find largest number in list, repeat process

[Raptor776]

Hello,

I wanted to ask how I could write a macro that would go through a list and find the largest number correlating with the values in A. For example

Name	Object
Apple	5
Apple	3
Apple	90
Orange	1
Orange	5
Orange	9
Orange	8
Orange	2
Mango	89
Mango	45

<tbody>
</tbody>

Therefore in Column C, it should display the largest value correlating with each set in A. So 90 in C4, 9 in C7, and 89 in C10.

The issue that I am having is that the number of rows over which the maximum formula applies is not constant. I was hoping to get some help with that. Thank you!

 

[NeonRedSharpie]

Function MAXIF(rng As Range, nCell As Range, val As Range) As Variant

    Dim arr As Variant
    arr = val


    For Each cell In rng
        If cell.Value = nCell.Value Then
            If val(cell.Row) > MAXIF Then MAXIF = val(cell.Row)
        End If
    Next cell
End Function

I don't know if you want a UDF, but if you do here is one. The input would look like:

=maxif($A$1:$A$10,$A1,$B$1:$B$10)

You can also call this code in an existing macro.

 

[steve the fish]

Probably few different ways but this in C2 and copied down:

=IF(B2=MAX((IF($A$2:$A$1000=A2,1,0)*$B$2:$B$1000)),MAX((IF($A$2:$A$1000=A2,1,0)*$B$2:$B$1000)),"")

CNTL-SHIFT-ENTER to enter

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG11Sep24
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn.Offset(, 1)
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn.Offset(, 1))
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k
[COLOR="Navy"]Dim[/COLOR] G
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] G [COLOR="Navy"]In[/COLOR] .Item(k)
        [COLOR="Navy"]If[/COLOR] G = Application.Max(.Item(k)) [COLOR="Navy"]Then[/COLOR]
            G.Offset(, 1) = Application.Max(.Item(k))
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] G
[COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Raptor776]

Hey all, thank you very much for all the formulas!!

Works perfectly!

 

[vds1]

Try In A2

=IF(SUMPRODUCT(MAX(--($A$2:$A$5000=A2)*$B$2:$B$5000))=B2,B2,"")