MAX Formula in VBA

[audrey]

Hello,

I have two columns in my excel sheet....

Column A
XYZ
ABC
DEF
XYZ
etc...

Column B
201010
201109
201007
etc...

I want to make a macro that will count only once Columns A values and match the MAX value in Column B.....


is that possible ??

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG24Nov57
[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("A1"), 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] .Add Dn.Value, Dn(, 2) Else .Item(Dn.Value) = Application.max(.Item(Dn.Value), Dn(, 2))
    [COLOR="Navy"]Next[/COLOR]
Range("C1").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .Items))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[audrey]

amazing thank you!

 

[audrey]

A step further Mick...

Lets say I have an extra column with country codes

Column A
UK
FRA
DE
US
UK
UK
FRA

Column B
XYZ
ABC
DEF
XYZ
DEF
BBB
AAA

Column C
201010
201109
201007
201104
201103
200105

Can I do the remove duplicates not necessarily based on Column B but also together with Column A and then and match the MAX value in Column C..... ??

 

[MickG]

Try this for results in "D,E & F".

[COLOR="Navy"]Sub[/COLOR] MG25Nov36
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), 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 & Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.Value & Dn.Offset(, 1).Value, Array(Dn, Dn(, 2), Dn(, 3))
        [COLOR="Navy"]Else[/COLOR]
         Q = .Item(Dn.Value & Dn.Offset(, 1).Value)
            Q(2) = Application.max(Q(2), Dn(, 3))
        .Item(Dn.Value & Dn.Offset(, 1).Value) = Q
       [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
Range("D1").Resize(.Count, 3) = Application.Transpose(Application.Transpose(.Items))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick