max array formula in vba

[prlondhe]

Hi

I have a sheet where column a contains a code, another sheets column p contains the age of the member. There can be 6 members under the code

Now i want to have a array formula in vba to find the max value in column p for code equal to code in sheet a.


sheet a
col a col b
111
112
and so on

sheet 2
col a col b col c
111 1 58
111 2 56
112 1 60
112 2 55
112 3 25
112 4 23
and so on

Can anybody help

 

[RickXL]

Hi,

Much as I like VBA i think a Worksheet Formula will be easiest:

Excel 2013
	A	B
1	ColA	ColB
2	111	58
3	112	60
4	113	0
Sheet1

Cell Formulas
Range	Formula
B2	{=MAX(IF(Sheet2!$A$2:$A$7=A2,Sheet2!$C$2:$C$7))}
B3	{=MAX(IF(Sheet2!$A$2:$A$7=A3,Sheet2!$C$2:$C$7))}
B4	{=MAX(IF(Sheet2!$A$2:$A$7=A4,Sheet2!$C$2:$C$7))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Excel 2013
	A	B	C
1	ColA	ColB	ColC
2	111	1	58
3	111	2	56
4	112	1	60
5	112	2	55
6	112	3	25
7	112	4	23
Sheet2

 

[prlondhe]

hI

Thank your for your reply. But I only want the max value for that particular code to perform more calculations. using a small VBA code to further calculate on the basis of max value. can u pls help on vba code

 

[RickXL]

It is difficult to provide a complete solution without knowing the complete problem.

The code below will look at the list in Sheet2 and find the maximum value for each column A value. That data is then held in a Dictionary where it can be looked up either by index number or by key. I have included an example of both.

Sub VBAMax()
    Dim arr  As Variant
    Dim dic  As Object
    Dim i    As Long
    Dim key  As String

    With ThisWorkbook.Worksheets("Sheet2")
        arr = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 3)
    End With
    
    Set dic = CreateObject("Scripting.dictionary")
    For i = 1 To UBound(arr)
        key = arr(i, 1)
        If dic(key) < arr(i, 3) Then dic(key) = arr(i, 3)
    Next
    
    key = 112
    MsgBox "Key: " & key & " has max " & dic(key)
    
    For i = 0 To dic.Count - 1
        Debug.Print dic.keys()(i); dic.items()(i)
    Next
End Sub

Regards,