Code Or Formula To Tell Me The Most For A Number In Column A

[Dazzawm]

I have column A below with a list of numbers, next in B is another list of numbers. I need a formula or code to tell me the highest amount for a said number. Below in rows 17-20 is the most for the numbers above. Thanks

Maybe the results could be put onto sheet 2 to appear like rows 17-20?

Excel 2010

	A	B
1	NCGZ007	1
2	NCGZ007	2
3	NCGZ007	4
4	NCGZ007	4
5	NCGZ007	1
6	AXGP421	4
7	NCGX103	4
8	NCGX80	4
9	NCGX103	2
10	NCGX80	2
11	NCGZ007	4
12	NCGZ007	3
13	AXGP421	4
14	AXGP421	1
15
16
17	NCGZ007	4
18	AXGP421	4
19	NCGX103	4
20	NCGX80	4

<tbody>
</tbody>

Sheet2​

 

[WaterGypsy]

if you can put a heading in row 1 then you could use a pivot table to show the max in Col B for each value in Col A

 

[steve the fish]

Use MAXIFS or MAX(IF if you dont have MAXIFS on your excel version.