Maximum Result for Each Name

[erenkey]

I have a spreadsheet that contains names in Column A and a number in Column B. What is the easiest way to determine the maximum number for each name in column A?

 

[danzon]

a pivot table

 

[stnkynts]

using Countif

 

[erenkey]

How would I use Countif? My data looks like this:

Cloumn A Column B
John 10
John 11
John 12
Chris 13
Chris 14
Chris 15
Bob 16
Bob 17
Bob 18

I want my result to show:

John 12
Chris 15
Bob 18

Each name could be in the list up to 3 times.

 

[T. Valko]

I have a spreadsheet that contains names in Column A and a number in Column B. What is the easiest way to determine the maximum number for each name in column A?

One way...

If you a unique list of the names:

Book1
	A	B	C	D	E
2	Name1	35	_	Name1	35
3	Name1	22	_	Name2	62
4	Name2	62	_	Name3	64
5	Name2	60	_	Name4	75
6	Name2	58	_	Name5	84
7	Name2	4	_	_	_
8	Name3	64	_	_	_
9	Name3	5	_	_	_
10	Name4	75	_	_	_
11	Name4	60	_	_	_
12	Name4	27	_	_	_
13	Name4	20	_	_	_
14	Name5	84	_	_	_
15	Name5	42	_	_	_
Sheet1

Enter this array formula** in E2 and copy down as needed:

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

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[erenkey]

that works fine if I find a unique list of names using a pivot table first. Is there a way to find the unique list of names, find the max value for each name and then sort descending by max value using a VBA code?

 

[T. Valko]

that works fine if I find a unique list of names using a pivot table first. Is there a way to find the unique list of names, find the max value for each name and then sort descending by max value using a VBA code?

I'm sure that could be done using VBA code but I'm also sure you'd want it done by someone who knows what they're doing! So, that eliminates me!

Maybe someone will come along and post some code to do this.

If not, after a day or 2 maybe repost and be very explicit when you do post telling us you want to do this with VBA code.

 

[danzon]

So, I assume you must have some good reason why you don't want to just use a simple pivot table like I posted in my first reply....

with that assumption.....

and reserving row 1 for column headers...so that the first actual name is in cell A2

we can build our unique list beginning in cell B2 using this array formula where LIST is a named region that holds your long list of names and duplicates

in cell B2
{=INDEX(LIST,MATCH(0,COUNTIF($B$1:B1,LIST),0))}

array formula : CTRL SHIFT ENTER

drag down as needed

then you can use the formula posted earlier by Biff in C2 and down to find corresponding max values...

 

[erenkey]

Sorry, I did not see your post earlier. The pivot table did not come through when I opened the thread. I can use a pivot table to find the unique name list and then use the Max/If formula to find the maximum value for each unique name. If I was the end user of this spreadsheet then I would stop there. I was trying to find a VBA code that would do everything including sorting the list descending so that the enduser would only have to run a Macro to get their answer.