Find Maximum Value for Each Name

[addepallibabu]

Hi,

I have the below table.

Name Number AA 1 AA 2 AA 3 AA 4 AA 5 BB 1 BB 2 BB 3 BB 4 BB 5 CC 1 CC 2 CC 3 CC 4 CC 5 <colgroup><col><col></colgroup><tbody> </tbody>

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

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

Name	Max Number
AA	5
BB	5
CC	5

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

 

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