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

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

=IF(COUNTIF($A$2:$A2,A2)1,"",MAX(IF($A$2:$A$999=A 2,$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