Default VBA to find the maximum value against a single criteria

[tuanbuffalo]

I need to find the maximum value present in column AC against multiple instances of variable X in column D, and populate the value in column F.

For example (apologies, I cannot share a true dataset due to the confidential nature of the data):

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column D[/TD]
[TD]...[/TD]
[TD]Column F[/TD]
[TD]...[/TD]
[TD]Column AC[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]...[/TD]
[TD]MaxSeverity[/TD]
[TD]...[/TD]
[TD]Severity[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]...[/TD]
[TD]50[/TD]
[TD]...[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]...[/TD]
[TD]50[/TD]
[TD]...[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B[/TD]
[TD]...[/TD]
[TD]30[/TD]
[TD]...[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]C[/TD]
[TD]...[/TD]
[TD]30[/TD]
[TD]...[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A[/TD]
[TD]...[/TD]
[TD]50[/TD]
[TD]...[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[TD]...[/TD]
[TD]30[/TD]
[TD]...[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]B[/TD]
[TD]...[/TD]
[TD]30[/TD]
[TD]...[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]C[/TD]
[TD]...[/TD]
[TD]30[/TD]
[TD]...[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]D[/TD]
[TD]...[/TD]
[TD]50[/TD]
[TD]...[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]



I've got a solution which uses nested CountIfs, but it takes around 45s on a dataset of 10k rows and is, well, inelegant to say the least.

Any suggestions from the community?

 

[Scott Huish]

I'm not sure why you would need VBA or nested COUNTIFs.

In Column F:
=MAX(IF($D$2:$D$10000=D2,$AC$2:$AC$10000))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.
Copy Down.

Excel 2010
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	ID	...	MaxSeverity	...																						Severity
2	A	...	50	...																						10
3	A	...	50	...																						50
4	B	...	30	...																						20
5	C	...	30	...																						30
6	A	...	50	...																						40
7	B	...	30	...																						30
8	B	...	30	...																						30
9	C	...	30	...																						30
10	D	...	50	...																						50
Sheet1

Cell Formulas
Range	Formula
F2	{=MAX(IF($D$2:$D$10000=D2,$AC$2:$AC$10000))}
F3	{=MAX(IF($D$2:$D$10000=D3,$AC$2:$AC$10000))}
F4	{=MAX(IF($D$2:$D$10000=D4,$AC$2:$AC$10000))}
F5	{=MAX(IF($D$2:$D$10000=D5,$AC$2:$AC$10000))}
F6	{=MAX(IF($D$2:$D$10000=D6,$AC$2:$AC$10000))}
F7	{=MAX(IF($D$2:$D$10000=D7,$AC$2:$AC$10000))}
F8	{=MAX(IF($D$2:$D$10000=D8,$AC$2:$AC$10000))}
F9	{=MAX(IF($D$2:$D$10000=D9,$AC$2:$AC$10000))}
F10	{=MAX(IF($D$2:$D$10000=D10,$AC$2:$AC$10000))}
Press CTRL+SHIFT+ENTER to enter array formulas.