Thread: a sort of GROUP BY data extraction question...

Example...

Data is in 3 columns. I have a drop-down list containing distinct
values from col 1 (A,B,C,D). On selection of a value from this list I
want find to find it's 2 highest values in column 2, and sum the
corresponding column 3 values. I'm guesssing this will be some horrible
kind of INDEX, MATCH, LARGE type array formula.


A,12,1
B,28,2
C,34,3
D,46,4
A,52,5
D,66,6
C,77,7
A,83,8
B,95,9
D,51,10
C,42,11

The best I could come up with yet is the following, for data in cells
A1:C20, and the required letter in E1:

=INDEX(C1:C20,MATCH(E1&LARGE(B1:B20*--(A1:A20=E1),1),A1:A20&B1:B20,0))

This will give the number in C:C for the highest value in B:B for the
given letter. Although I suspected it would not work, because it gives
a nested loop, I tried enclosing it in SUM and replacing LARGE(..., 1)
with
LARGE(..., ROW(1:2))

but it only returns the highest number and ignores the other number. If
the problem is for only two values then you can use the above formula
twice and sum the two i.e.
INDEX(..., 1 ,...) + INDEX(..., 2, ...)
but I cannot see how this could be expanded for bigger numbers.

HTH
Kostis Vezerides

Depending on exactly why/howoften you need this data, you might just do a
Autofilter on Columns A ad B, and then sum C.........

Vaya con Dios,
Chuck, CABGx3



"jc" wrote:

> Example...
>
> Data is in 3 columns. I have a drop-down list containing distinct
> values from col 1 (A,B,C,D). On selection of a value from this list I
> want find to find it's 2 highest values in column 2, and sum the
> corresponding column 3 values. I'm guesssing this will be some horrible
> kind of INDEX, MATCH, LARGE type array formula.
>
>
> A,12,1
> B,28,2
> C,34,3
> D,46,4
> A,52,5
> D,66,6
> C,77,7
> A,83,8
> B,95,9
> D,51,10
> C,42,11
>
>