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.

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

<colgroup><col><col><col span="4"><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col span="4"><col></colgroup><tbody></tbody>

<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>

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) :-

KEYCODE		Value 1	Value 2	Value 3	Value 4
230069021		26	62	35	114
230069021		128	18	64	93	**
230069021		14	31	110	67
230069021		99	101	102	55
230069021		111	6	92	113
230069021		69	34	77	8
230069021		47	48	73	5
230069021		2	58	12	72

<tbody>
</tbody>

 

[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 ...