Large formula by criteria

[Matt_Chelmsford]

Hi,

I have a data set of about 500 lines

In Column B I have contract codes
In Column C I have account numbers
In Column T I have total costs

For each Contract, I need to extract the 10 largest accounts value and return the account number to another sheet.

If it was just one contract and no criteria, then this formula would work me, but the criteria has stumped me!

=OFFSET(T1,match(Large(T:T,1),T:T,0),-17,1,1)

Any help given will be gratefully appreciated

Matthew

 

[steve the fish]

Cant you use a pivot table?

 

[Matt_Chelmsford]

Hi Steve, Others will be consuming the data and it will change. It is the easiest solution without doubt, but does not really fit in to what I am trying to achieve here!

 

[steve the fish]

I presume then that the contract codes can be repeated within the data?

 

[Aladin Akyurek]

Book1
	B	C	D
1	contact	acc #	tcosts
2	c100	ACC-1	54
3	c101	ACC-3	29
4	c102	ACC-4	52
5	c103	ACC-7	64
6	c104	ACC-2	50
7	c105	ACC-09	59
8	c106	ACC-12	54
9	c107	ACC-20	52
10	c108	ACC-11	30
11	c109	ACC-17	49
12	c110	ACC-35	50
13
Sheet1

Book1
	A	B
1	top
2	5
3	6
4	largest accounts	account #
5	64	ACC-7
6	59	ACC-09
7	54	ACC-1
8	54	ACC-12
9	52	ACC-4
10	52	ACC-20
11
Sheet2

Change A2 of Sheet2 to 10 for your data.

In A3 of Sheet2 control+shift+enter, not just enter:

=COUNTIFS(Sheet1!D2:D12,">="&LARGE(Sheet1!D2:D12,MIN(A2,COUNT(Sheet1!D2:D12))))

In A5 of Sheet2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$5:A5)>$A$3,"",LARGE(Sheet1!$D$2:$D$12,ROWS($A$5:A5)))

In B5 of Sheet2 control+shift+enter, not just enter, and copy down:

=IF($A5="","",INDEX(Sheet1!$C$2:$C$12,SMALL(IF(Sheet1!$D$2:$D$12=$A5,ROW(Sheet1!$C$2:$C$12)-ROW(Sheet1!$C$2)+1),COUNTIFS($A$5:A5,A5))))