Find MAX number with criteria
 

Hello all:

Using Excel 2003

I have two lists, column A contains a list of names that have reoccurrences of the same name many times within the list and I’ve already made a list in column E of those unique names. Column B is a list of numbers in random order and no two numbers are the same.

My problem is trying to make a new list in column F by finding the MAX number in column B that is associated with the persons name in column E.

I’ve tried many different formulas with no success, can any one please help me out with this.

Find MAX number with criteria
 

Try this array formula** :

=MAX(IF(A$1:A$100=E1,B$1:B$100))

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

Copy down as needed.

--
Biff
Microsoft Excel MVP


"GoBow777" wrote in message
...

Hello all:

Using Excel 2003

I have two lists, column A contains a list of names that have
reoccurrences of the same name many times within the list and I've
already made a list in column E of those unique names. Column B is a
list of numbers in random order and no two numbers are the same.

My problem is trying to make a new list in column F by finding the MAX
number in column B that is associated with the persons name in column
E.

I've tried many different formulas with no success, can any one please
help me out with this.




--
GoBow777

Find MAX number with criteria
 

Let's say the names are in A2:A140, the numbers in B2:B140
Let's say the first name in E is in E2

In F2 enter =MAX(IF($A$2:$A$140=E2,$B$2:$B$140))
But since this is an array formula commit it with CTRL+SHIFT+ENTER not just
ENTER
The formula bar will display {=MAX(IF($A$2:$A$140=E2,$B$2:$B$140))} but
Excel not you has added the braces

Copy F2 down the column

Do NOT try to use full columns references =MAX(IF(A:A=E2,B:B)) unless you
are working in Excel 2007
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"GoBow777" wrote in message
...

Hello all:

Using Excel 2003

I have two lists, column A contains a list of names that have
reoccurrences of the same name many times within the list and I’ve
already made a list in column E of those unique names. Column B is a
list of numbers in random order and no two numbers are the same.

My problem is trying to make a new list in column F by finding the MAX
number in column B that is associated with the persons name in column
E.

I’ve tried many different formulas with no success, can any one please
help me out with this.




--
GoBow777

Find MAX number with criteria
 

Try one of these:

=MAX(INDEX((A1:A100=E1)*B1:B100,))
or
=SUMPRODUCT(MAX((A1:A100=E1)*B1:B100))

just press ENTER


"GoBow777" wrote:


Hello all:

Using Excel 2003

I have two lists, column A contains a list of names that have
reoccurrences of the same name many times within the list and Ive
already made a list in column E of those unique names. Column B is a
list of numbers in random order and no two numbers are the same.

My problem is trying to make a new list in column F by finding the MAX
number in column B that is associated with the persons name in column
E.

Ive tried many different formulas with no success, can any one please
help me out with this.




--
GoBow777

Find MAX number with criteria
 

Hello,

I suggest to use my UDF Mfreq:
http://www.sulprobil.com/html/mfreq.html

Please note that this is an array function. Select your desired output
area and then enter the function with all necessary parameters and
finish with CTRL + SHIFT + ENTER, not only ENTER.

Regards,
Bernd

Thanks to all who replied, everything works great now. Thank you.