Selecting Unique Items Subject to a Constraint

  • Thread starter Thread starter cmiedaner
  • Start date Start date
C

cmiedaner

Hi.

I am trying to do the following...

I have a column list with 1 column that lists 4 Product
Groups ( Group1, Group2, Group3, Group4). Call this
List 1.

AND. I have a column list with 3 columns that list (1)
Product Groups, (2)Product, and (3)Quantity. Call this
List 2.

I would like to see if excel can look at List 1 and for
each of the 4 Product Groups, select from List 2 the
Product with the greatest Quantity. In the case where the
greatest Quantity is equal for a given Product Group, I do
not care which Product is selected for List 1.

This is a simplification of the task I am trying to do.
List 1 actually has 3000 Product Groups and List 2 has
25,000 Products.
 
cmiedaner,
Assumed you List2 is in ColumnAA:ColumnCC
this array formula gives the Max Quantity for Group1
=MAX((AA2:AA100="group1")*(AC2:AC100))
adjust the range and hit Enter while keeping Ctrl and Shift keys pressed
this array formula gives the Last Row where Max Quantity for Group1 is found
=MAX((AA2:AA100="group1")*(AC2:AC100=MAX((AA2:AA100="group1")*(AC2:AC100)))*
(ROW(A2:A100)))
watch for the line warp in the mail all should be in one line
Cecil
 
Sure, Excel can do that.

But you need to use two extra columns: one column to link your two
tables together, and one to identify the maximum.

Let's say List 2 is in columns A to C, with headers in row 1 and data
below. Let's also say that "List1" is a named range - note that there
are no spaces in the name.

In cell D2, use the formula
=NOT(ISERROR(MATCH(A2,List1,FALSE)))

In cell E2, array enter using Ctrl-Shift-Enter the formula

=C2=MAX(($A$2:$A$???=A2)*$C$2:$C$???)

replacing both ???s with the actual last row of List 2.

Copy cells D2:E2 down to match List 2, then use Data | Filter |
Autofilter on List 2 (with the two new columns) and filter columns D
and E for TRUE.

If you have ties, that will show all products whose quantity is equal
to the max - you didn't care which, but we'll show you all....

HTH,
Bernie
 
Back
Top