Need help displaying name in new cell based on MAX value

[joec4571]

My speadsheet looks like:

Last Name	First Name	Grade	Week 1 Contribution	Week 2 Contribution	Week 3 Contribution	Week 4 Contribution	Total Contribution
Blackman	Ciarra	1st Grade	$10.00				$10.00
Olanipekun	Oluwatoyin	1st Grade	$20.00				$20.00

<tbody>
</tbody>

I have a cell using MAX function ie =MAX(I412:I488), I also want to display the last and first name of the person with the highest contribution in a new cell as well. Cant figure out how to do that.

 

[mcwillis81]

Hi, Try this

Option Explicit

Sub Find_Name_of_Highest_Value_In_Range()

Dim ChkValue As Long, Rg As Range, ChkAddress As String
Dim Cell As Range, fName As String, lName As String

Set Rg = Sheet1.Range("d2:" & (Sheet1.Range("d2").End(xlDown).Address))

ChkValue = 0

For Each Cell In Rg
    If Cell.Value > ChkValue Then
        ChkValue = Cell.Value
        ChkAddress = Cell.Address
    End If
Next

fName = Sheet1.Range(ChkAddress).Offset(0, -2).Value
lName = Sheet1.Range(ChkAddress).Offset(0, -3).Value

MsgBox ChkAddress & " " & fName & " " & lName

End Sub

I tested it on a sample. You will need to change the range to suite your own spreadsheet etc.

Hope this helps,

Cheers

Martin

 

[Scott T]

This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done properly excel will put {} around the formula.
Assuming last name in column A and first name in column B

=INDEX($B$2:$B$5,MATCH(MAX(I2:I5),I2:I5,0))&" "&INDEX($A$2:$A$5,MATCH(MAX(I2:I5),I2:I5,0))

 

[joec4571]

Thank you! This is new to me, where would I enter and edit this code?

Heres a link to a copy of the spreadsheet, scroll down to E494, Im trying to populate those fields based on max vale for each grade:

https://docs.google.com/spreadsheets/d/1Q57ApBzChLHvzhx0rU8YezZOhzGIA3cx2DXqteHEtcU/edit?usp=sharing

Hi, Try this

Option Explicit

Sub Find_Name_of_Highest_Value_In_Range()

Dim ChkValue As Long, Rg As Range, ChkAddress As String
Dim Cell As Range, fName As String, lName As String

Set Rg = Sheet1.Range("d2:" & (Sheet1.Range("d2").End(xlDown).Address))

ChkValue = 0

For Each Cell In Rg
    If Cell.Value > ChkValue Then
        ChkValue = Cell.Value
        ChkAddress = Cell.Address
    End If
Next

fName = Sheet1.Range(ChkAddress).Offset(0, -2).Value
lName = Sheet1.Range(ChkAddress).Offset(0, -3).Value

MsgBox ChkAddress & " " & fName & " " & lName

End Sub

I tested it on a sample. You will need to change the range to suite your own spreadsheet etc.

Hope this helps,

Cheers

Martin

 

[Scott T]

First you need to enable macros.
https://support.office.com/en-us/ar...ocuments-7b4fdd2e-174f-47e2-9611-9efe4f860b12

To put the developer tab on the ribbon.
https://msdn.microsoft.com/en-us/library/bb608625.aspx

To open the visual basic editor from the developer tab select Visual basic or the use the short cut Alt+F11.

Insert a new module by selecting Insert and Module.
then past the code

To run the code you can use the short cut Alt+F8 or the macros button on the developer tab of the ribbon. then select the macro you want to run.

Test this code on a copy of your data since you can not undo what a macro does.

 

[mcwillis81]

Hi

I've had a look at your spreadsheet and to be honest the solution from Scott T fits best here.

The vba option is over complicated compared to the using the INDEX function. I've added the first couple of cells in the summary at the bottom so you can see how it works.

The only difference is that Scott's formula needed to be cut in half as it originally got both first and last name in one go.

If you still want to explore the VBA option then let me know

Cheers,

Martin