IF formula with MAX function

[yo eddy]

I'm trying to clean up some data that is rather dirty and could use some help here. What I would like to do is deduplicate data but with certain 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 entry 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 to 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 somehow create a scenario where the MAX function is executed only on the array 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 company would go here** , max value, delete))

ANy suggestions would greatly be appreciated.

Eddy

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



yo eddy wrote:
>
> I'm trying to clean up some data that is rather dirty and could use some
> help here. What I would like to do is deduplicate data but with certain
> 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 entry
> 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 to
> 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 somehow
> create a scenario where the MAX function is executed only on the array
> 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 company
> would go here** -, max value, delete))
>
> ANy suggestions would greatly be appreciated.
>
> Eddy
>
> --
> yo eddy
> ------------------------------------------------------------------------
> yo eddy's Profile: http://www.excelforum.com/member.php...o&userid=35243
> View this thread: http://www.excelforum.com/showthread...hreadid=550170

--

Dave Peterson