The solutions below are dependent on the coded data "E.g. 95(AN)" in specific columns for each code: AN, AF, AP, SP.

These get the AVE and SUM, but are dependent on columns having the same code, and then the result doesn't display the code, only the number.

This code depends that "E.g. (AN)" are in every 4th column A, E, I and M.

Same for the SUM...

This code below does a GREAT job getting the #1 Largest and 2nd Largest code AND it displays the result WITH the code "E.g. 98(AP)", BUT is still dependant on the coded data in certain columns...

Then I just use the formula MAXNTH(Range,1) for MAX and MAXNTH(Range,2) for 2nd largest.

E.g. MAXNTH(A1:D1,2)

- Option Explicit
- Function MAXNTH(NumRange As Range, Optional Nth As Long = 1)
- Dim k, kk, i As Long, c As Long, r As Long
- c = NumRange.Columns.Count
- r = NumRange.Rows.Count
- If c > 1 And r > 1 Then
- MAXNTH = CVErr(xlErrNum)
- Exit Function
- End If
- If c > 1 Then
- If Nth > c Then
- MAXNTH = CVErr(xlErrNum)
- Exit Function
- End If
- k = NumRange.Parent.Evaluate("transpose(transpose(" & NumRange.Address & "))")
- ElseIf r > 1 Then
- If Nth > r Then
- MAXNTH = CVErr(xlErrNum)
- Exit Function
- End If
- k = NumRange.Parent.Evaluate("transpose(" & NumRange.Address & ")")
- End If
- With CreateObject("scripting.dictionary") 'used dictionary object to get the unique items
- .comparemode = 1 'not case sensitive
- For i = 1 To UBound(k)
- kk = Split(Replace(k(i), ")", vbNullString), "(") 'split the item with delimiter '(' also replace ')' with null
- .Item(Trim(kk(1))) = .Item(Trim(kk(1))) + CDbl(kk(0)) 'add the value for each item
- Next
- c = .Count
- If c Then
- k = .keys: kk = .items 'get the keys and items in respective array variables
- If Nth > c Then
- MAXNTH = CVErr(xlErrNum)
- Exit Function
- End If
- c = Application.Large(kk, Nth) 'get the Nth largest
- r = Application.Match(c, kk, 0) 'match the position of Nth in the array
- MAXNTH = c & "(" & k(r - 1) & ")" 'using the position get the Item
- End If
- End With
- End Function

---------------------------------------------------

I need to be able to (consider) the code in the AVE and SUM calculations and not be dependent on codes (AN, AF, AP, SP) being in their own columns.

I want to end up with the AVE and TOTAL SUM of HIGHEST (most often) code occurrence...

(0s are not considered)

E.g.

A B C D E F SUM of largest occurrence AVE of largest occurrence

0(AN) 96(AN) other data other data 95(AP) 93(AN) 189(AN) 95(AN)

Here's more examples of source data and columns attached: