IF formula with MAX function

[yo eddy]

I'm trying to clean up some data that is rather dirty and could use som
help here. What I would like to do is deduplicate data but with certai
criteria. I have duplicate company names and each company has a value
For all the same companies I'd like to just select or mark the entr
with the maximum number of users.

Her is a same of the data

COMPANY NAME USERS
Company A 1
Company A 7
Company A 3
Company B 15
Company B 86
Company B 2
Company B 2
Company B 51
Company C 3
Company C 5
Company C 11

COMPANY NAME is column a and USER values is in column b. I'd like t
return the MAX USER values in column c

I would like the output to look like:
COMPANY NAME MAX USERS
Company A 7
Company B 86
Company C 11

I've tried to combine the IF formula and MAX but I need to someho
create a scenario where the MAX function is executed only on the arra
of formulas for the similar company names. Maybe hold it in an array
which I don't know how to do.Maybe VB or VBA could solve this?

Here is the formula in theory:
=if(and(a3=a4), MAX: **-the range of values for each similar compan
would go here** -, max value, delete))

ANy suggestions would greatly be appreciated.

Edd

 

[Dave Peterson]

I put this in C2 (headers in row 1):

=IF(COUNTIF($A$2:$A2,A2)>1,"",MAX(IF($A$2:$A$999=A2,$B$2:$B$999)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

And drag it down the column.