Conditional max. number

[dennis]

i've got a excel table like this:
partcode value
201 0100
302 0019
147 0001
201 0098
201 0102
147 0002

i would like a cell that returns the max. no. in the "value" column
for a particular partcode. Say, if i want to know the max. value for
partcode "201", it will return "0102". How to do it if the table will
get longer and longer (ie. more partcode to enter) day after day ?

jack

 

[Biff]

Hi Dennis!

If the "values" are in fact numeric numbers formatted to
include the leading zeros:

=MAX(IF(A1:A6=201,B1:B6))

If the "values" are actually text numbers, try:

=MAX(IF(A1:A6=201,--B1:B6))

Both formulas entered as an array - CTRL,SHIFT,ENTER

These formulas will drop any leading zeros, so you'll have
to format the cell appropriately.

Biff

 

[Frank Kabel]

Hi
use the array formula (entered with cTRL+SHIFT+ENTER):
=MAX(IF(A1:A100=201,B1:B100))

 

[Max]

One approach ..

Assume your table as posted is in Sheet1,
cols A and B, data from row2 down,
and in col B are *numbers* custom formatted as "0000"

partcode value
201 0100
302 0019
147 0001
201 0098
201 0102
147 0002

Enter a label in C1: ValueText
Put in C2: =TEXT(B2,"0000")
Copy C2 down

[This col C is created to illustrate the scenario
if the "numbers" in col B are *text*]

Let's create 3 dynamic ranges
for cols A to C in Sheet1:

Partcode :
=OFFSET(Sheet1!$A$1,,,SUMPRODUCT((Sheet1!$A$1:$A$65535<>"")
*1),1)

ValNum :
=OFFSET(Sheet1!$B$1,,,SUMPRODUCT((Sheet1!$B$1:$B$65535<>"")
*1),1)

ValText :
=OFFSET(Sheet1!$C$1,,,SUMPRODUCT((Sheet1!$C$1:$C$65535<>"")
*1),1)

To create the dynamic ranges,
click Insert > Name > Define
and in the dialog "Define Name":

Enter the Name in the "Names in workbook" box,
Paste the formula in the "Refers to" box
Click Add
(Repeat steps to add another name)

---------
In Sheet2
---------
Assume the partcodes will be entered in A2 down:

Put in A2: 201

Put in B2: =MAX(IF(Partcode=A2,ValNum))
[Array-enter]

Put in C2: =MAX(IF(Partcode=A2,ValText+0))
[Array-enter]

Array-entering the formulas in B2 and C2 means:
Press CTRL+SHIFT+ENTER, instead of just pressing ENTER

Done correctly, Excel will wrap curly braces { }
around the formula, viz. it will appear:

In B2:{=MAX(IF(partcode=A2,ValNum))}
In C2:{=MAX(IF(partcode=A2,ValText+0))}

Do not type-in the curly braces!

Custom format B2 and C2 as "0000"

Select B2:C2 and copy down

Cols B and C will return the max value for the partcodes
listed in col A from the table in Sheet1

The use of dynamic ranges will take care of the
table becoming longer day-by-day in Sheet1

 

[Guest]

The formatting of the cells will have no impact on the formula
Bob Umlas, Excel MVP

 

[Max]

One approach ..

Assume your table as posted is in Sheet1,
cols A and B, data from row2 down,
and in col B are *numbers* custom formatted as "0000"

partcode value
201 0100
302 0019
147 0001
201 0098
201 0102
147 0002

Enter a label in C1: ValueText
Put in C2: =TEXT(B2,"0000")
Copy C2 down

[This col C is created to illustrate the scenario
if the "numbers" in col B are *text*]

Let's create 3 dynamic ranges
for cols A to C in Sheet1:

Partcode :
=OFFSET(Sheet1!$A$1,,,SUMPRODUCT((Sheet1!$A$1:$A$65535<>"")
*1),1)

ValNum :
=OFFSET(Sheet1!$B$1,,,SUMPRODUCT((Sheet1!$B$1:$B$65535<>"")
*1),1)

ValText :
=OFFSET(Sheet1!$C$1,,,SUMPRODUCT((Sheet1!$C$1:$C$65535<>"")
*1),1)

To create the dynamic ranges,
click Insert > Name > Define
and in the dialog "Define Name":

Enter the Name in the "Names in workbook" box,
Paste the formula in the "Refers to" box
Click Add
(Repeat steps to add another name)

---------
In Sheet2
---------
Assume the partcodes will be entered in A2 down:

Put in A2: 201

Put in B2: =MAX(IF(Partcode=A2,ValNum))
[Array-enter]

Put in C2: =MAX(IF(Partcode=A2,ValText+0))
[Array-enter]

Array-entering the formulas in B2 and C2 means:
Press CTRL+SHIFT+ENTER, instead of just pressing ENTER

Done correctly, Excel will wrap curly braces { }
around the formula, viz. it will appear:

In B2:{=MAX(IF(partcode=A2,ValNum))}
In C2:{=MAX(IF(partcode=A2,ValText+0))}

Do not type-in the curly braces!

Custom format B2 and C2 as "0000"

Select B2:C2 and copy down

Cols B and C will return the max value for the partcodes
listed in col A from the table in Sheet1

The use of dynamic ranges will take care of the
table becoming longer day-by-day in Sheet1

 

[Biff]

Hi Bob!

The formatting of the cells will have no impact on the
formula

Well, it did when I tested! Both formulas returned 102 and
I had to format the cell to get 0102.

Biff

 

[dennis]

Thks Max !
i've got the idea of using "max-if" array.
However, i've found the output is not correct. i'm not sure if the
dynamic range part is having problem. Kindly one more help.

One approach ..

Assume your table as posted is in Sheet1,
cols A and B, data from row2 down,
and in col B are *numbers* custom formatted as "0000"

partcode value
201 0100
302 0019
147 0001
201 0098
201 0102
147 0002

Enter a label in C1: ValueText
Put in C2: =TEXT(B2,"0000")
Copy C2 down

[This col C is created to illustrate the scenario
if the "numbers" in col B are *text*]

Let's create 3 dynamic ranges
for cols A to C in Sheet1:

Partcode :
=OFFSET(Sheet1!$A$1,,,SUMPRODUCT((Sheet1!$A$1:$A$65535<>"")
*1),1)

ValNum :
=OFFSET(Sheet1!$B$1,,,SUMPRODUCT((Sheet1!$B$1:$B$65535<>"")
*1),1)

ValText :
=OFFSET(Sheet1!$C$1,,,SUMPRODUCT((Sheet1!$C$1:$C$65535<>"")
*1),1)

To create the dynamic ranges,
click Insert > Name > Define
and in the dialog "Define Name":

Enter the Name in the "Names in workbook" box,
Paste the formula in the "Refers to" box
Click Add
(Repeat steps to add another name)

---------
In Sheet2
---------
Assume the partcodes will be entered in A2 down:

Put in A2: 201

Put in B2: =MAX(IF(Partcode=A2,ValNum))
[Array-enter]

Put in C2: =MAX(IF(Partcode=A2,ValText+0))
[Array-enter]

Array-entering the formulas in B2 and C2 means:
Press CTRL+SHIFT+ENTER, instead of just pressing ENTER

Done correctly, Excel will wrap curly braces { }
around the formula, viz. it will appear:

In B2:{=MAX(IF(partcode=A2,ValNum))}
In C2:{=MAX(IF(partcode=A2,ValText+0))}

Do not type-in the curly braces!

Custom format B2 and C2 as "0000"

Select B2:C2 and copy down

Cols B and C will return the max value for the partcodes
listed in col A from the table in Sheet1

The use of dynamic ranges will take care of the
table becoming longer day-by-day in Sheet1

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

i've got a excel table like this:
partcode value
201 0100
302 0019
147 0001
201 0098
201 0102
147 0002

i would like a cell that returns the max. no. in the "value" column
for a particular partcode. Say, if i want to know the max. value for
partcode "201", it will return "0102". How to do it if the table will
get longer and longer (ie. more partcode to enter) day after day ?

jack
.

 

[Max]

Maybe you could post a "readable" version
of your email here.

I'll send you a sample book with the construct described
via private email.

 

[dennis]

Max,
You may write to (e-mail address removed).
Thanks in advance.

 

[Max]

Sample book on it's way to you ..