# Find largest number in list, repeat process

#### Raptor776

##### New Member
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!

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Code:
``````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:

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

You can also call this code in an existing macro.

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

Try this:-
Code:
``````[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]
[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

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

Works perfectly!

Try In A2

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

Replies
10
Views
898
Replies
6
Views
991
Replies
6
Views
534
Replies
5
Views
508
Legacy 143009
L
Replies
7
Views
198

1,221,218
Messages
6,158,595
Members
451,501
Latest member
andysacko

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back