Find Maximum Value for Each Name

[addepallibabu]

Hi,

I have the below table.


[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][TABLE="width: 137"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]


I want to find maximum value for each name. For example as shown below. Kindly help me someone.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Max Number[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[NARAYANK991]

Hi ,

Try this array formula , to be entered using CTRL SHIFT ENTER :

=MAX(IF(Sheet1!$A$2:$A$16 = Sheet2!A2, Sheet1!$B$2:$B$16))

Enter this in cell B2 in Sheet2 , and copy down.

 

[Aladin Akyurek]

In E2 control+shift+enter, not just enter, and copy down:

=MAX(IF($A$2:$A$16=D2,$B$2:$B$16))

If you have MAXIFS, in D2 just enter and copy down:

=MAXIFS($B$2:$B$16,$A$2:$A$16,D2)

 

[Nishant94]

Just another alternative:

Unknown
	A	B	C	D	E
1	Name	Number		Name	Max Number
2	AA	1		AA	5
3	AA	2		BB	5
4	AA	3		CC	5
5	AA	4
6	AA	5
7	BB	1
8	BB	2
9	BB	3
10	BB	4
11	BB	5
12	CC	1
13	CC	2
14	CC	3
15	CC	4
16	CC	5
Sheet5

Cell Formulas
Range	Formula
E2	=AGGREGATE(14,6,$B$2:$B$16/($A$2:$A$16=D2),1)

 

[addepallibabu]

Thank you NARAYANK991! Great solution!!

 

[addepallibabu]

Hey thank you Aladin Akyurek. Indeed simple and effective

 

[addepallibabu]

Super Nishant Ghosh! Thank you for efforts.