Finding the largest value

[Excel_77]

Hi All,

What code can I use to return the ID (column A) that has the highest associated total from column B? Note, that some IDs are duplicated so the overall totals are needed for each.

Thanks

What

 

[Cubist]

I would create a Pivot table and then select a cell in the "Total" column -> Right-click -> Sort -> Largest to smallest.

 

[Excel_77]

Thanks, are there any other options beyond a pivot table?

 

[Cubist]

In 2019, the formula option isn't pretty, but VBA is another option.

Sub SumValuesByID()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim dict As Object
    Dim data As Variant
    Dim maxID As String
    Dim maxValue As Double

    Set dict = CreateObject("Scripting.Dictionary")
    Set ws = ThisWorkbook.Sheets("Sheet1") 'Change sheet name to suit
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    data = ws.Range("A2:B" & lastRow).Value 'data starts in row 2.
   
   'Loop and sum
    For i = 1 To UBound(data, 1) 
        If dict.exists(data(i, 1)) Then
            dict(data(i, 1)) = dict(data(i, 1)) + data(i, 2)
        Else
            dict.Add data(i, 1), data(i, 2)
        End If
    Next i
   
    'Find the max ID and amount
    maxValue = 0
    For Each ID In dict.Keys
        If dict(ID) > maxValue Then
            maxValue = dict(ID)
            maxID = ID
        End If
    Next ID
   
   'Output
    ws.Range("C1").Value = maxID
    ws.Range("D1").Value = maxValue

    Set dict = Nothing
End Sub

 

[Excel_77]

In 2019, the formula option isn't pretty, but VBA is another option.

Sub SumValuesByID()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim dict As Object
    Dim data As Variant
    Dim maxID As String
    Dim maxValue As Double

    Set dict = CreateObject("Scripting.Dictionary")
    Set ws = ThisWorkbook.Sheets("Sheet1") 'Change sheet name to suit
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    data = ws.Range("A2:B" & lastRow).Value 'data starts in row 2.
  
   'Loop and sum
    For i = 1 To UBound(data, 1)
        If dict.exists(data(i, 1)) Then
            dict(data(i, 1)) = dict(data(i, 1)) + data(i, 2)
        Else
            dict.Add data(i, 1), data(i, 2)
        End If
    Next i
  
    'Find the max ID and amount
    maxValue = 0
    For Each ID In dict.Keys
        If dict(ID) > maxValue Then
            maxValue = dict(ID)
            maxID = ID
        End If
    Next ID
  
   'Output
    ws.Range("C1").Value = maxID
    ws.Range("D1").Value = maxValue

    Set dict = Nothing
End Sub

I don't use VBA.

 

[Cubist]

I don't use VBA.

Then I don't know what you're looking.