Max Value from long range of date based on unique key

[MarcMermans]

Hello,


I need some help from the experts on this forum.

As seen in the next example i need to known the line with the highest value for the keycode, in this example i have marked the row with an *.
The keycode can exist once ore more than once and the complete list is around 60 000 rows. This is not a one time solution because i want to use this function regulary.

Is i't possible to write a VBA for this.



[TABLE="width: 502"]
<colgroup><col><col><col span="4"><col></colgroup><tbody>[TR]
[TD]KEYCODE[/TD]
[TD][/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Value 3[/TD]
[TD]Value 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230069021[/TD]
[TD][/TD]
[TD]26[/TD]
[TD]62[/TD]
[TD]35[/TD]
[TD]114[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230069021[/TD]
[TD][/TD]
[TD]128[/TD]
[TD]18[/TD]
[TD]64[/TD]
[TD]93[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD]230069021[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]31[/TD]
[TD]110[/TD]
[TD]67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230069021[/TD]
[TD][/TD]
[TD]99[/TD]
[TD]101[/TD]
[TD]102[/TD]
[TD]55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230069021[/TD]
[TD][/TD]
[TD]111[/TD]
[TD]6[/TD]
[TD]92[/TD]
[TD]113[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230069021[/TD]
[TD][/TD]
[TD]69[/TD]
[TD]34[/TD]
[TD]77[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230069021[/TD]
[TD][/TD]
[TD]47[/TD]
[TD]48[/TD]
[TD]73[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230069021[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]58[/TD]
[TD]12[/TD]
[TD]72[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230011211[/TD]
[TD][/TD]
[TD]39[/TD]
[TD]29[/TD]
[TD]127[/TD]
[TD]36[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230011211[/TD]
[TD][/TD]
[TD]90[/TD]
[TD]124[/TD]
[TD]74[/TD]
[TD]117[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD]230011211[/TD]
[TD][/TD]
[TD]45[/TD]
[TD]4[/TD]
[TD]116[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230011211[/TD]
[TD][/TD]
[TD]42[/TD]
[TD]79[/TD]
[TD]59[/TD]
[TD]83[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230011211[/TD]
[TD][/TD]
[TD]57[/TD]
[TD]107[/TD]
[TD]98[/TD]
[TD]23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230011211[/TD]
[TD][/TD]
[TD]112[/TD]
[TD]84[/TD]
[TD]106[/TD]
[TD]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230011211[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]82[/TD]
[TD]91[/TD]
[TD]71[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230011211[/TD]
[TD][/TD]
[TD]16[/TD]
[TD]76[/TD]
[TD]19[/TD]
[TD]21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230011211[/TD]
[TD][/TD]
[TD]28[/TD]
[TD]109[/TD]
[TD]94[/TD]
[TD]78[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230022954[/TD]
[TD][/TD]
[TD]60[/TD]
[TD]53[/TD]
[TD]54[/TD]
[TD]33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230022954[/TD]
[TD][/TD]
[TD]121[/TD]
[TD]104[/TD]
[TD]80[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230022954[/TD]
[TD][/TD]
[TD]118[/TD]
[TD]66[/TD]
[TD]126[/TD]
[TD]11[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD]230022954[/TD]
[TD][/TD]
[TD]68[/TD]
[TD]15[/TD]
[TD]108[/TD]
[TD]120[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230013270[/TD]
[TD][/TD]
[TD]87[/TD]
[TD]13[/TD]
[TD]122[/TD]
[TD]22[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD]230013270[/TD]
[TD][/TD]
[TD]49[/TD]
[TD]51[/TD]
[TD]17[/TD]
[TD]70[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230013270[/TD]
[TD][/TD]
[TD]65[/TD]
[TD]103[/TD]
[TD]85[/TD]
[TD]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230013270[/TD]
[TD][/TD]
[TD]63[/TD]
[TD]52[/TD]
[TD]41[/TD]
[TD]44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230013270[/TD]
[TD][/TD]
[TD]37[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]43[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230020079[/TD]
[TD][/TD]
[TD]38[/TD]
[TD]115[/TD]
[TD]95[/TD]
[TD]88[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD]230020079[/TD]
[TD][/TD]
[TD]46[/TD]
[TD]30[/TD]
[TD]56[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230020080[/TD]
[TD][/TD]
[TD]27[/TD]
[TD]96[/TD]
[TD]3[/TD]
[TD]119[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD]230020123[/TD]
[TD][/TD]
[TD]25[/TD]
[TD]75[/TD]
[TD]86[/TD]
[TD]97[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230020123[/TD]
[TD][/TD]
[TD]123[/TD]
[TD]125[/TD]
[TD]81[/TD]
[TD]89[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD]230020123[/TD]
[TD][/TD]
[TD]105[/TD]
[TD]32[/TD]
[TD]9[/TD]
[TD]61[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 390"]
<colgroup><col><col span="4"><col></colgroup><tbody>[TR]
[TD][TABLE="width: 502"]
<colgroup><col><col><col span="4"><col></colgroup><tbody></tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance.

 

[MickG]

Try this for Double "**" in column "G".

[COLOR="Navy"]Sub[/COLOR] MG14Dec06
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dic.Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] Application.Max(Dn.Offset(, 2).Resize(, 4)) > _
        Application.Max(Dic(Dn.Value).Offset(, 2).Resize(, 4)) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = Dn
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] Dic.keys
    Dic(K).Offset(, 6) = "**"
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[James006]

Hi Mick,

It looks like your code is indeed picking all Max numbers ... with the exception of the very key ...
What is the reason ???

 

[MickG]

I'm Not sure I understand your point !!!
My understanding of the question is:- The Unique "Keys" columns is there to separate the sets of numbers, so that the max row marked represents the row with max value, for each set of numbers (not including the key number) with the same "Key" value

 

[James006]

For the first Keycode 230069021, the Max is located in cell B3 ... whereas the macro indicates row 2 ...

 

[MickG]

Max for 230069021= 128 shown below, as result from code on row (3) :-

[TABLE="width: 530"]
<tbody>[TR]
[TD]KEYCODE[/TD]
[TD][/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Value 3[/TD]
[TD]Value 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]230069021[/TD]
[TD][/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]114[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]230069021[/TD]
[TD][/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]93[/TD]
[TD]**[/TD]
[/TR]
[TR]
[TD="align: right"]230069021[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]230069021[/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]230069021[/TD]
[TD][/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]113[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]230069021[/TD]
[TD][/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]230069021[/TD]
[TD][/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]230069021[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]72[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Mick ... you are right ...

At my end, the macro marks row 2 ... as if 114 was the max ???

 

[MickG]

I think the reason is:_
When I paste the data on my sheet, column "B" is Blank and the numbers run from columns "C to F", so if your data start in column "B" then the code will miss that and start on column "C".
Good shout though !!!!

 

[James006]

Thanks a lot for the explanation ... !!! since I was quite disturbed ...